39.       总是使用索引的第一个列

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.

译者按:

这也是一条简单而重要的规则. 见以下实例.

SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));

Table created.

SQL> create index multindex on multiindexusage(inda,indb);

Index created.

SQL> set autotrace traceonly

SQL>  select * from  multiindexusage where inda = 1;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'

   2    1     INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)

SQL> select * from  multiindexusage where indb = 1;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'

很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

40.       ORACLE内部操作

当执行查询时,ORACLE采用了内部的操作. 下表显示了几种重要的内部操作.

ORACLE Clause

内部操作

ORDER BY

SORT ORDER BY

UNION

UNION-ALL

MINUS

MINUS

INTERSECT

INTERSECT

DISTINCT,MINUS,INTERSECT,UNION

SORT UNIQUE

MIN,MAX,COUNT

SORT AGGREGATE

GROUP BY

SORT GROUP BY

ROWNUM

COUNT or COUNT STOPKEY

Queries involving Joins

SORT JOIN,MERGE JOIN,NESTED LOOPS

CONNECT BY

CONNECT BY

[1] [2] [3]  下一页