使用索引优化排序

使用索引优化排序

1. 创建测试数据
sqlplus / as sysdba

SQL> create table lixia.t1 as select * from dba_objects;

Table created.


SQL>connect lixia/lixia
SQL> create table t2 as select * from t1;

Table created.

SQL> create index idx_t1_1 on t1(object_name,object_id);

Index created.

SQL> create index idx_t2_1 on t2(object_name,object_id);


SQL> exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');


2. 测试
sqlplus / as sysdba
SQL> set autotrace traceonly


2.1 测试 WHERE 条件中不使用索引的首字段,ORDER 语句中使用索引首字段，测试是否可以使用索引。
SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 order by object_name;

98 rows selected.




通过上面的测试我们看到索引在  ORDER BY 中使用了索引首字段（OBJECT_NANE），但在
WHERE 字句中没有使用索引的首字段导致无法使用索引。

2.2 在WHERE 字句和 ORDER BY 中使用的是相同字段（索引首字段），是可以正常使用索引的。
SQL> select object_id,object_name,OWNER from lixia.t1 where object_name='T1' order by object_name;




2.3 WHERE 字句中只使用了索引首字段，ORDER BY 中使用了索引的第二个字段，可以正常使用索引，但
    还是产生了排序操作
SQL> create index idx_t1_2 on t1(object_id,object_name);

Index created.

SQL>  exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

PL/SQL procedure successfully completed.



SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100  order by object_name;

98 rows selected.



2.4 WHERE 字句中使用了索引的第一个索引，但在 ORDER BY 字句中使用了索引的第三个字段，此时仍然使用了
    INDEX RANGE SCAN，但时没有消除排序

SQL> drop index idx_t1_2;

Index dropped.

SQL> create index idx_t1_3 on t1(object_id,owner,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

PL/SQL procedure successfully completed.

SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100  order by object_name;

98 rows selected.





2.5 IDX_T1_3 包含 OBJECT_ID,OWERN,OBJECT_NAME，WHERE 字句中使用了索引首字段 OBJECT_ID，ORDER BY 使用了
    索引的第三个字段 OBJECT_NAME，我们看到执行计划中实际值使用了索引的首字段，并没有消除排序。

SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name;

no rows selected







2.6 索引 IDX_T1_1 包含 OBJECT_NAME,OBJECT_ID,WHERE 字句中使用了OBJECT_NAME、OBJECT_ID两个字段，ORDER BY 使用了OBJECT_NAME字段，
    在执行计划中使用索引消除了排序。

SQL> drop index lixia.idx_t1_3;

Index dropped.

SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name;

no rows selected




2.7 ORDER BY 中字段的顺序必须与索引字段顺序完全一致才能使用索引消除排序。在 WHERE 字句中只要使用到了索引的首字段，即便
    WHERE 字句中字段顺序与索引字段顺序不一致也可以正常使用索引消除全表扫描


下面的SQL中 ORDER BY 没有使用索引首字段 OBJECT_NAME，所以排序没有被消除
SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_id;

no rows selected






下面的SQL ORDER BY 中虽然有使用 OBJECT_NAME（索引首字段），但 OBJECT_NAME不是 ORDER BY 字句中的第一个字段
所以没有消除排序

SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_id,object_name;

no rows selected





下面的SQL ORDER BY 中 OBJECT_NAME（索引首字段）是第一个字段，索引正常使用索引消除了排序

SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name,object_id;

no rows selected





SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name;

no rows selected





2.8 虽然索引首字段在 ORDR BY 的第一个位置，但ORDER BY 中使用了不在索引中的字段，所以无法消除排序

SQL> select object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name,owner;

no rows selected







SQL> drop index lixia.idx_t1_1;

Index dropped.

SQL> create index lixia.idx_t1_3 on lixia.t1(object_name,owner,object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

PL/SQL procedure successfully completed.


下面SQL ORDER BY 第一位置和第二个位置使用的是索引的第个字段和第二个字段（ORDR BY 没有使用索引的第三个字段）,因为与索引
字段顺序一致索引可以正常使用所以消除排序

SQL> select  object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name,owner;

no rows selected







下面SQL ORDER BY 第一位置和第二个位置使用的是索引的第个字段和第三个字段（ORDR BY 没有使用索引的第二个字段）,因为与索引
字段顺序步一致所以不可以正常使用索引消除排序
SQL> select  object_id,object_name,OWNER from lixia.t1 where object_id<100 and object_name in ('T1','T2','T10','T100','dba_objects') order by object_name,object_id;

no rows selected






2.9 当使用两个表的字段进行排序时是否可以正常使用索引消除排序操作



select t1.object_id,t1.object_name,t2.owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name='T1'
  and t2.object_name='T1'
order by t1.object_name,t2.object_name





2.10 当CBO通过SQL语句可以判断需要排序的数据只有一种值，并且 ORDER BY 字句使用的字段没有索引，
     也不会进行排序操作

SQL> drop index lixia.idx_t1_1;

Index dropped.

SQL> drop index lixia.idx_t2_1;

Index dropped.

SQL> create index lixia.idx_t1_1 on lixia.t1(object_id);

Index created.

SQL> create index lixia.idx_t2_1 on lixia.t2(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('LIXIA','T2',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');


select t1.object_id,t1.object_name,t2.owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name ='T1'
  and t2.OBJECT_ID<100
  and t1.OBJECT_ID<100
  and t2.object_name='T1'
order by t1.object_name;






select t1.object_id,t1.object_name,t2.owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name ='T1'
  and t2.OBJECT_ID<100
  and t1.OBJECT_ID<100
  and t2.object_name='T1'
order by t1.object_name,t2.object_name;






下面的语句 WHERE 字句中 t1.object_name in ('T1','T2') 条件导致 CBO无法在SQL语句解析阶段知道OBJECT_NAME只有一种值，
所以进行了排序操作

select t1.object_id,t1.object_name,t2.owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name in ('T1','T2')  --这个IN 条件导致 CBO无法在SQL语句解析阶段知道OBJECT_NAME只有一种值
  and t2.OBJECT_ID<100
  and t1.OBJECT_ID<100
  and t2.object_name='T1'
order by t1.object_name;





2.11 在表连接的语句中，当只使用一个表的字段进行排序并且有对应索引时可以消除排序，但如果使用多个表的字段排序，即便每个表的排序
     字段有对应的索引也无法消除排序操作

select t1.object_id,t1.object_name,t2.owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name in ('T1','T2')
  and t2.OBJECT_ID<100
  and t1.OBJECT_ID<100
  and t2.object_name='T1'
order by t1.object_id




select t1.object_id,t1.object_name,t2.owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name in ('T1','T2')
  and t2.OBJECT_ID<100
  and t1.OBJECT_ID<100
  and t2.object_name='T1'
order by t1.object_id,t2.object_id;





注意下面SQL中用来排序的 T2.OBJECT_ID 是没索引的

select * from (
select t1.object_id,t1.object_name,t2.owner,t2.object_id t2_object_id,t2.object_name t2_object_name from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  and t1.object_name in ('T1','T2')
  and t2.OBJECT_ID<100
  and t1.OBJECT_ID<100
  and t2.object_name='T1'
order by t1.object_id)
order by t2_object_name;




对于使用多个表字段进行排序可以先使用一个表的字段排序，然后把结果封装到子查询中，在主查询中再使用
另一个表的字段进行排序，这样就可以消除排序操作了


SQL> create index lixia.idx_t2_2 on lixia.t2(object_id,owner);

Index created.

SQL> exec dbms_stats.gather_table_stats('LIXIA','T1',cascade=>true,no_invalidate=> FALSE,estimate_percent => 100,method_opt=>'for all columns size repeat');

PL/SQL procedure successfully completed.

select t1.object_id,t1.object_name,t2.owner,t2.object_id t2_object_id,t2.object_name t2_object_name,
t2.owner t2_owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  --and t1.object_name in ('T1','T2')
  and t2.OBJECT_ID<200
  and t1.OBJECT_ID<200
  --and t2.object_name='T1'
order by t1.object_id






select * from (
select t1.object_id,t1.object_name,t2.owner,t2.object_id t2_object_id,t2.object_name t2_object_name,
t2.owner t2_owner from lixia.t1,lixia.t2
where t1.object_id=t2.object_id
  --and t1.object_name in ('T1','T2')
  and t2.OBJECT_ID<200
  and t1.OBJECT_ID<200
  --and t2.object_name='T1'
order by t1.object_id)
order by t2_object_id,t2_owner;






测试证明这种方法是无法消除排序操作。



下面的测试表明把在一个子查询中查询T1表的数据并排序，再另一个子查询中查询T2表并排序，
然后再把两个结果集连接仍然无法消除排序
不会这种写法逻辑多比其他写法有下降

select a.object_id,a.object_name,b.owner from
(select object_id,object_name
from lixia.t1
where OBJECT_ID<200
order by OBJECT_ID) a,
(select object_id,owner
from lixia.t2
where OBJECT_ID<200
order by object_id,owner) b
where a.object_id=b.object_id;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2122077/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2122077/