索引学习四--降序索引

1. 建测试表s_table，并在empno和ename列上创建唯一索引

scott@TEST>create table s_table as select * from test where empno<10;
 
Table created.
 
scott@TEST>create unique index idx_s on s_table(empno,ename);
 
Index created.
 
scott@TEST>select empno,ename from s_table where empno between 1 and 5
  2  and ename is not null order by empno,ename;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   INDEX (RANGE SCAN) OF 'IDX_S' (UNIQUE)
 
 
    上面的查询中，由于索引中的两列已经做了排序，因此在生成的执行计划中，没有排序操作。

2. 改变查询，按照empno降序，ename升序来查询。

scott@TEST>select empno,ename from s_table where empno between 1 and 5
  2  and ename is not null order by empno desc,ename asc;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     INDEX (RANGE SCAN) OF 'IDX_S' (UNIQUE)
 
    生成的执行计划中，需要进行排序操作，这是因为oracle无法再使用empno、ename列上的索引对数据排序，只能找到所有的行，然后进行排序操作。
 

3. 删除原来的索引，并创建新的降序索引

scott@TEST>drop index idx_s;
 
Index dropped.
 
scott@TEST>create unique index idx_s on s_table(empno desc,ename asc);
 
Index created.
 
scott@TEST>select empno,ename from s_table where empno between 1 and 5
  2   and ename is not null order by empno desc,ename asc;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'S_TABLE'
 
 
 
scott@TEST>exec dbms_stats.gather_index_stats(user,'IDX_S');
 
PL/SQL procedure successfully completed.
 
scott@TEST>select empno,ename from s_table where empno between 1 and 5
  2  and ename is not null order by empno desc,ename asc;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'S_TABLE'
 
    注意在上述操作中，即使对索引做了最新的分析，但查询的执行计划仍然没有发生变化，尝试对表做分析：
 
scott@TEST>exec dbms_stats.gather_table_stats(user,'S_TABLE');
 
PL/SQL procedure successfully completed.
 
scott@TEST>select empno,ename from s_table where empno between 1 and 5
  2  and ename is not null order by empno desc,ename asc;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=3 Bytes=24)
   1    0   INDEX (RANGE SCAN) OF 'IDX_S' (UNIQUE) (Cost=1 Card=3 Byte
          s=24)

      执行计划已经发生了变化，说明降序索引起了作用，同时，也可以注意到，在改变了索引的结构后，如果只是对索引进行重新分析，并不能影响执行计划的产生，只有对相关的表做重新分析后，执行计划才会按照最新的索引统计信息生成。  

     以上测试环境：

sys@TEST>select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

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

转载于:http://blog.itpub.net/10972173/viewspace-274658/