更好/更快的方法是:加入多个表或选择一个大表 [英] What is better/faster: joining many tables or select one big table

问题描述

我们正在使用Oracle 11，这是用Java编写的应用程序.每天一次，通常是下午，由于许多大的sql查询，我们的数据库冻结.我想以某种方式优化此查询.该查询由不同表的许多联接组成.我的问题是:使用左联接是否会更好地提高性能，还是将所有信息放在一个表中并使用一个选择项会更好?假设我将为此表建立一个良好的索引.

We are using Oracle 11, our application written on Java. Once a day, usually afternoon our database freezing because of many big sql queries. I want optimize this queries somehow. This queries consists of many joins of different tables. My question is: is it better for performance to use left join, or it is better put all information in one table and use one select? Suppose I will build a good indexes for this table.

有关信息:平均而言，一个查询可获取100兆字节.另外，此查询有时会互相锁定

For information: in average, one query fetch 100 megabytes. Also this queries lock each other sometimes

更新

连接8个表的表，它们是具有3-5列的普通表，其中一列是一些ID. sql查询看起来像:

Table that are joining 8 tables,they are usual tables with 3-5 columns, one of the column are some ID. Sql query looks like:

SELECT t1.c1,t2.c5, t6.c2, ... FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1
LEFT JOIN t3 ON t3.c1 = t2.c2
LEFT JOIN t4 ON t4.c1 = t2.c1
LEFT JOIN t5 ON t5.c5 = t1.c1
LEFT JOIN t6 ON t6.c1 = t2.c1
LEFT JOIN t7 ON t7.c3 = t3.c1
LEFT JOIN t8 ON t8.c1 = t2.c1
WHERE something

我的问题是，是否最好创建一个由所有联接表组成的新表，并使用如下查询:

My question is, is it better to create one new tables, that consist of all joining tables, and use query like this:

SELECT c1,c5, c2, ... FROM SOME_NEW_TABLE

更新2

这里是报告，如果有人可以对此进行一般性的解释，那就太好了.

Here is report, it would be great if some one can explain it in general.

推荐答案

我认为这个问题通常可以得到回答.在性能调优这类查询时，您需要考虑许多事项:

The question can be generally answered I think. In performance tuning this type of query you will a number of things to consider:

为该语句建立执行计划需要多长时间?如果查询第一次运行缓慢，而之后又一直快速运行，则解析时间是一个问题.我假设查询中没有更改的常量.如果不是，请使用绑定变量，或者最后使用动态绑定变量，但是自动引入绑定变量可能不是一个好主意，请参阅更改会话集cursor_sharing = similar".

How long does it take to establish an execution plan for the statement? If the query runs slow the first time and fast all times later, parse time is an issue. I assume that there are no changing constants in the query. If not, please use bind variables or as a last resort use dynamic bind variables, but I can be a bad idea to automatically introduce bind variables , see "alter session set cursor_sharing=similar".

尤其是对于较旧的版本和许多联接(Oracle 8在分析具有6个以上相似标识联接的语句时确实很糟糕....)分析时间可能会很昂贵. Oracle 11通常会在考虑了许多执行计划后停止运行，从而缩短了解析时间.在Oracle 11上，解析时间仍然可能是个问题，尤其是对于工会/工会.

Especially with older versions and many joins (Oracle 8 was really bad in parsing statements with more than a 6 similar identity joins....) parse time can be expensive. Oracle 11 typically cuts the parse time by stopping after a number of execution plans have been considered. On Oracle 11 parse time still can be an issue, especially with union/union all.

此外，在此查询中，您使用ANSI样式联接.请注意，在将更优雅的ANSI样式的联接与复杂语句一起使用时，Oracle 11具有一些性能缺陷.因此，对于自动生成的语句，我建议使用Oracle样式(c(+)= d)，对于需要维护的语句，您需要研究它是否确实存在问题.

Also, in this query you use ANSI style joins. Note that Oracle 11 has some performance drawbacks when using the more elegant ANSI style joins with complex statements. For automatically generated statements I therefore recommend Oracle style (c (+) = d), for statements that need to be maintained you need to study whether it really is a problem.

当解析时间成为问题时，我建议使用/* +有序*/之类的提示作为起点.这样一来，确保您的连接顺序使得临时生成的数据量尽可能少，并且存在正确的索引.

When parse time is an issue, I would recommend using a hint like /*+ ordered */ as a starting point. With this make sure your join order is such that the amount is data produced temporarily is as little as possible and the correct indexes are present.

在执行期间，Oracle执行执行计划.与其他数据库平台相比，Oracle确实做到了这一点.但是，如果执行计划很糟糕，执行就需要时间.在您的问题中，您谈到是否要预先加入所有内容.

During execution, Oracle executes the execution plan. Oracle does this really efficient compared to other database platforms. But if the execution plan stinks, the execution takes time. In your question you talk about whether to prejoin everything or not.

通常，最好始终从完全规范化的模型开始.在完全规范化的模型中，数据仅存储一次.因此，当有效地计划查询时，处理的数据量最少.假定Oracle服务器有足够的内存来缓存它的全部或大部分，因为联接策略有时需要在内存中有很多工作空间以及已经从磁盘获取的数据.

In general it is best to always start of with a fully normalized model. In a fully normalized model data is stored only once. So when the query is efficiently planned, the least amount of data is processed. This assumes that the Oracle server has sufficient memory to cache it all or large parts of it, since join strategies need sometimes a lot of work space in memory plus the data already fetched from disk.

当性能不足时，我将首先介绍一些提示，但要保留归一化模型.在临时步骤中，请始终尽量减少符合输出条件的数据量.如果它确实不起作用，则可以使用派生表，但是我发现这通常是开发技能薄弱的标志.

When performance is insufficient, I would start by introducing hints but staying with the normalized model. Always try to keep the amount of data eligible for output during interim steps as small as possible. When it really doesn't work, you might go for a derived table but I find this generally a sign of weak development skills.

在所有这一切中，我假设启动执行计划的一个表的数据量很大，而另一个表的数据量较小，可能要小一些或小得多.如果不是，您正在运行"Wiebertje"查询(我没有其他名称，它是荷兰糖果的形状).然后，请阅读第9页以及会议演示2006

In all this, I am assuming that one of the tables that start the execution plan has a large data volume and the other are smaller, maybe a little smaller or a lot smaller. If not, you are running a "Wiebertje" query (I don't have another name for it, it is shape of Dutch candy). Then please read page 9 and further of conference presentation 2006

在周期结束时，Oracle会在某一时刻开始发送回数据.尤其是该卷可以极大地转移全部内容所需的时间.应用程序获取绝对的所有信息并不罕见，而仅显示前50行.请引入窗口或获取显示的水印+常量"以减少获取时间.您可能需要在语句或会话中引入诸如/* + first_rows */之类的提示以进行交互使用.

At the end of the cycle, Oracle starts sending back the data at some moment. Especially the volume can highly the time needed to transfer it all. It is not uncommon for applications to fetch absolutely everything, but only displaying the first 50 rows. Please introduce windowing or "fetch to displayed watermark + constant" to reduce the fetch time. You may need to introduce a hint such as /*+ first_rows */ in the statement or session for interactive use.

这篇关于更好/更快的方法是:加入多个表或选择一个大表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！