Oracle认证：增加Distinct提高查询效率

会计考友 · 发表于 2012-8-4 13:41:06

只有增添DISTINCT关头字，Oracle必然需要对后面的所有字段进行排序。以前也经常发现因为开发人员对SQL不是很理解，在SELECT列表的20多个字段前面添加了DISTINCT，造成发芽根基上不成能执行完成，甚至发生ORA-7445错误。所以一向向开发人员强调DISTINCT给机能带来的影响。　　没想到开发人员在测试一条年夜的SQL的时辰，告诉我如不美观加上了DISTINCT，则发芽概略需要4分钟摆布可以执行完，如不美观不加DISTINCT，则发芽执行了10多分钟，仍然得不到结不美观。
　　首先想到的是可能DISTINCT是在子发芽中，因为加上了DISTINCT，将第一步结不美观集缩小了，导致发芽机能提高，结不美观一看SQL，发现DISTINCT居然是在发芽的最外层。
　　因为原始SQL太长，而且牵扯的表太多，很难说清嚣张，这里模拟了一个例子，这个例子因为数据量和SQL的复杂水平限制，无法看出二者执行时刻上的较着分歧。这老迈两种情形的逻辑读对比来声名问题。
　　首先成立模拟情形：　
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS
　　2 WHERE OWNER = 'SYS'
　　3 AND OBJECT_TYPE NOT LIKE '%BODY'
　　4 AND OBJECT_TYPE NOT LIKE 'JAVA%';
　　Table created.
　　SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SEGMENTS WHERE OWNER = 'SYS';
　　Table created.
　　SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE OWNER = 'SYS';
　　Table created.
　　SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (OBJECT_NAME);
　　Table altered.
　　SQL> CREATE INDEX IND_T2_SEGNAME ON T2(SEGMENT_NAME);
　　Index created.
　　SQL> CREATE INDEX IND_T3_TABNAME ON T3(TABLE_NAME);
　　Index created.
　　SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)
　　PL/SQL procedure successfully completed.
　　SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T2', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)
　　PL/SQL procedure successfully completed.
　　SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T3', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE)
　　PL/SQL procedure successfully completed.
　　仍然沿用膳缦沔例子中的结构，看看原始SQL和增添DISTINCT后的分歧：

SQL> SET AUTOT TRACE
　　SQL> SELECT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
　　2 FROM T1, T2
　　3 WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
　　4 AND T1.OBJECT_NAME IN
　　5 (
　　6 SELECT INDEX_NAME FROM T3
　　7 WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME
　　8 );
　　311 rows selected.
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=668 Bytes=62124)
　　1 0 HASH JOIN (SEMI) (Cost=12 Card=668 Bytes=62124)
　　2 1 HASH JOIN (Cost=9 Card=668 Bytes=39412)
　　3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
　　4 2 TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)
　　5 1 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340 Bytes=11560)
　　Statistics
　　----------------------------------------------------------

会计考友 · 发表于 2012-8-4 13:41:07

</p>　　0 recursive calls
　　0 db block gets
　　93 consistent gets
　　0 physical reads
　　0 redo size
　　8843 bytes sent via SQL*Net to client
　　723 bytes received via SQL*Net from client
　　22 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　311 rows processed
　　SQL> SELECT DISTINCT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
　　2 FROM T1, T2
　　3 WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
　　4 AND T1.OBJECT_NAME IN
　　5 (
　　6 SELECT INDEX_NAME FROM T3
　　7 WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME
　　8 );
　　311 rows selected.
　　Execution Plan
　　----------------------------------------------------------
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=93)
　　1 0 SORT (UNIQUE) (Cost=16 Card=1 Bytes=93)
　　2 1 HASH JOIN (Cost=12 Card=1 Bytes=93)
　　3 2 HASH JOIN (Cost=5 Card=668 Bytes=44088)
　　4 3 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340 Bytes=11560)
　　5 3 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
　　6 2 TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　72 consistent gets
　　0 physical reads
　　0 redo size
　　8843 bytes sent via SQL*Net to client
　　723 bytes received via SQL*Net from client
　　22 SQL*Net roundtrips to/from client
　　1 sorts (memory)
　　0 sorts (disk)
　　311 rows processed

年夜统计信息可以看出，添加了DISTINCT后，语句的逻辑读反而比不加DISTINCT要高。为什么会发生这种情形，还要年夜执行打算说起。
　　不加DISTINCT的情形，因为使用IN子发芽的发芽，Oracle对第二个毗连采用了HASH JOIN SEMI，这种HASH JOIN SEMI相对于通俗的HASH JOIN，价钱要年夜一些。
　　而添加了DISTINCT之后，Oracle知道最终必定要进行排序去重的操作，是以在毗连的时辰就选择了HASH JOIN作为了毗连体例。这就是为什么加上了DISTINCT之后，逻辑读反而削减了。可是同时，加上了DISTINCT之后，语句增添了一个排序操作，而在不加DISTINCT的时辰，是没有这个操作的。
　　当毗连的表数据量很年夜，可是SELECT的最终结不美观不是良多，且SELECT列的个数不是良多的时辰，加上DISTINCT之后，这个排序的价钱要小于SEMI JOIN毗连的价钱。这就是增添一个DISTINCT操作发芽效率反而提高，这个似乎不成能发生的情形的真正原因。
　　最后需要声名一下，这篇文丈廒声名，优化的时辰没有什么工具是一成不变的，几乎任何工作都有可能发生，不要被一些所谓死轨则限制住。年夜白了这一点就可以了。这篇文章并不是筹算供给一种优化SQL的体例，严酷意义年夜将，加上DISTINCT和不加DISTINCT是两个完全分歧的SQL语句。虽然在这个例子中，二者是等价的，可是这是表结构、约束前提和数据自己配合限制的结不美观。换了另一个情形，这两个SQL获得的结不美观可能会相去甚远，所以，不要试图将本文的例子作为优化时的一种体例。

		自动登录	找回密码
密码			立即注册

[其他] Oracle认证：增加Distinct提高查询效率

Oracle认证：增加Distinct提高查询效率