[20191209]降序索引疑问.txt

[20191209]降序索引疑问.txt --//今天优化一个项目,我发现许多表里面有有隐含字段,一般开发很少建立函数索引.我自己检查发现里面存在大量的降序索引. --//我感觉有点奇怪,为什么开发要建立大量降序索引有什么好处呢? --//关于降序索引我以前写的: http://blog.itpub.net/267265/viewspace-2221425/=>[20181123]关于降序索引问题.txt http://blog.itpub.net/267265/viewspace-2221527/=>[20181124]关于降序索引问题2.txt http://blog.itpub.net/267265/viewspace-2221529/=>[20181124]关于降序索引问题3.txt http://blog.itpub.net/267265/viewspace-2221532/=>[20181124]关于降序索引问题4.txt http://blog.itpub.net/267265/viewspace-1159181/=>[20140512]关于降序索引.txt 1.环境: SCOTT@book> @ ver1 PORT_STRING         VERSION        BANNER ------------------- -------------- ---------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> alter system set pga_aggregate_target=4G; System altered. SCOTT@book> create table t (id1 number,id2 number,vc varchar2(100)); Table created. SCOTT@book> create index i_t_id1 on t(id1); Index created. SCOTT@book> create index i_t_id2 on t(id2 desc); Index created. SCOTT@book> insert into t select rownum,rownum,lpad('a',100,'a') from dual connect by level<=1e6; 1000000 rows created. SCOTT@book> commit ; Commit complete. SCOTT@book> select index_name,index_type,blevel,leaf_blocks,distinct_keys from dba_indexes where owner=user and index_name like 'I_T_ID%'; INDEX_NAME INDEX_TYPE            BLEVEL LEAF_BLOCKS DISTINCT_KEYS ---------- --------------------- ------ ----------- ------------- I_T_ID2    FUNCTION-BASED NORMAL      2        4283       1000000 I_T_ID1    NORMAL                     2        1999       1000000 --//明显可以发现降序索引I_T_ID2占用块许多.这个是因为我查询的数据是有序的(与他们的情况类似),降序索引的分裂50-50%,而正常索引是90-10分裂. --//所以在导入数据时,降序索引占用块数多.而如果插入数据是增序的情况索引占索引块数少. --//注意两者的块争用都是一样,只不过一个在索引树的左边(降序),一个在索引树的右边. 2.我仔细扫描执行语句,有点类似如下: --//select * from (select * from t where id1<=1e6 order by id1 desc) where rownum<=100; SCOTT@book> alter session set statistics_level = all; Session altered. SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 desc) where rownum<=100; ... SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8ns53ghu2vkcz, child number 1 ------------------------------------- select * from (select * from t where id1<=1e6 order by id1 desc) where rownum<=100 Plan hash value: 768900824 ------------------------------------------------------------------------------------------------------------------------------------ | Id  | Operation                      | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT               |         |      1 |        |       |     5 (100)|          |    100 |00:00:00.01 |       8 | |*  1 |  COUNT STOPKEY                 |         |      1 |        |       |            |          |    100 |00:00:00.01 |       8 | |   2 |   VIEW                         |         |      1 |    100 |  7800 |     5   (0)| 00:00:01 |    100 |00:00:00.01 |       8 | |   3 |    TABLE ACCESS BY INDEX ROWID | T       |      1 |   1000K|   105M|     5   (0)| 00:00:01 |    100 |00:00:00.01 |       8 | |*  4 |     INDEX RANGE SCAN DESCENDING| I_T_ID1 |      1 |    100 |       |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       4 | ------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2 / T@SEL$2    4 - SEL$2 / T@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<=100)    4 - access("ID1"<=1000000) SCOTT@book> select * from (select * from t where id2<=1e6 order by id2 desc) where rownum<=100; ... SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  0xc0uzzqsk6bp, child number 0 ------------------------------------- select * from (select * from t where id2<=1e6 order by id2 desc) where rownum<=100 Plan hash value: 3930323544 ----------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                     | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |         |      1 |        |       |     5 (100)|          |    100 |00:00:00.01 |       8 | |*  1 |  COUNT STOPKEY                |         |      1 |        |       |            |          |    100 |00:00:00.01 |       8 | |   2 |   VIEW                        |         |      1 |    100 |  7800 |     5   (0)| 00:00:01 |    100 |00:00:00.01 |       8 | |   3 |    TABLE ACCESS BY INDEX ROWID| T       |      1 |   1000K|   105M|     5   (0)| 00:00:01 |    100 |00:00:00.01 |       8 | |*  4 |     INDEX RANGE SCAN          | I_T_ID2 |      1 |    100 |       |     3   (0)| 00:00:01 |    100 |00:00:00.01 |       4 | ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2 / T@SEL$2    4 - SEL$2 / T@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<=100)    4 - access("T"."SYS_NC00004$">=HEXTORAW('3BFDFF') )        filter(SYS_OP_UNDESCEND("T"."SYS_NC00004$")<=1000000) --//可以发现两者都能很好地使用对应索引.降序索引毫无优势可言.取消where条件: SCOTT@book> select * from (select * from t  order by id1 desc) where rownum<=1;        ID1        ID2 VC ---------- ---------- ----------------------------------------------------------------------------------------------------    1000000    1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dyd579rap5r4q, child number 0 ------------------------------------- select * from (select * from t  order by id1 desc) where rownum<=1 Plan hash value: 3299198703 ------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |      |      1 |        |       |       | 29544 (100)|          |      1 |00:00:00.42 |   16227 |       |       |          | |*  1 |  COUNT STOPKEY          |      |      1 |        |       |       |            |          |      1 |00:00:00.42 |   16227 |       |       |          | |   2 |   VIEW                  |      |      1 |   1000K|    74M|       | 29544   (1)| 00:05:55 |      1 |00:00:00.42 |   16227 |       |       |          | |*  3 |    SORT ORDER BY STOPKEY|      |      1 |   1000K|   105M|   120M| 29544   (1)| 00:05:55 |      1 |00:00:00.42 |   16227 |   119M|  3708K|          | |   4 |     TABLE ACCESS FULL   | T    |      1 |   1000K|   105M|       |  4402   (1)| 00:00:53 |   1000K|00:00:00.12 |   16227 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------------------- SCOTT@book> select * from (select * from t  order by id2 desc) where rownum<=1;        ID1        ID2 VC ---------- ---------- ----------------------------------------------------------------------------------------------------    1000000    1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  85pmrga0pr2jd, child number 0 ------------------------------------- select * from (select * from t  order by id2 desc) where rownum<=1 Plan hash value: 3299198703 ------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |      |      1 |        |       |       | 29544 (100)|          |      1 |00:00:00.39 |   16227 |       |       |          | |*  1 |  COUNT STOPKEY          |      |      1 |        |       |       |            |          |      1 |00:00:00.39 |   16227 |       |       |          | |   2 |   VIEW                  |      |      1 |   1000K|    74M|       | 29544   (1)| 00:05:55 |      1 |00:00:00.39 |   16227 |       |       |          | |*  3 |    SORT ORDER BY STOPKEY|      |      1 |   1000K|   105M|   120M| 29544   (1)| 00:05:55 |      1 |00:00:00.39 |   16227 |   119M|  3708K|          | |   4 |     TABLE ACCESS FULL   | T    |      1 |   1000K|   105M|       |  4402   (1)| 00:00:53 |   1000K|00:00:00.11 |   16227 |       |       |          | ------------------------------------------------------------------------------------------------------------------------------------------------------------- --//两者都走全表扫描.因为NULL的问题,select * from (select * from t  order by id1 desc) where rownum<=1一定不会i_t_id1索引. --//我以前做过类似测试,按照道理降序索引是包含NULL值的,可以降序索引一样没用.你可以做如下测试: --//参考:http://blog.itpub.net/267265/viewspace-1159181/=>[20140512]关于降序索引.txt SCOTT@book> select /*+ index(t i_t_id2) */ id2 from t  where id2 is null ; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  5k10f4kkvknmr, child number 0 ------------------------------------- select /*+ index(t i_t_id2) */ id2 from t  where id2 is null Plan hash value: 1601196873 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |  4402 (100)|          |      0 |00:00:00.07 |   16227 | |*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |     5 |  4402   (1)| 00:00:53 |      0 |00:00:00.07 |   16227 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ID2" IS NULL) --//还是走全表扫描.实际上NULL在索引I_T_ID2里面的.如果写成如下: SCOTT@book> select /*+ index(t i_t_id2) */ * from t  where sys_op_descend(id2)=hextoraw('00') ; no rows selected --//这样写就是查询id2 is NULL. SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8xc58b4jcqk3f, child number 0 ------------------------------------- select /*+ index(t i_t_id2) */ * from t  where sys_op_descend(id2)=hextoraw('00') Plan hash value: 3974417878 --------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | --------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |      1 |        |       |     4 (100)|          |      0 |00:00:00.01 |       3 | |   1 |  TABLE ACCESS BY INDEX ROWID| T       |      1 |      1 |   117 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       3 | |*  2 |   INDEX RANGE SCAN          | I_T_ID2 |      1 |      1 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       3 | --------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1    2 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("T"."SYS_NC00004$"=HEXTORAW('00') ) --//可以这样写法是可以使用I_T_ID2索引的.我估计问题可能出在id2 is null这样的写法上. 3.修改为not NULL,继续测试: SCOTT@book> alter table t modify (id1 not null); Table altered. SCOTT@book> alter table t modify (id2 not null); Table altered. SCOTT@book> select * from (select * from t  order by id1 desc) where rownum<=1;        ID1        ID2 VC ---------- ---------- ----------------------------------------------------------------------------------------------------    1000000    1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  dyd579rap5r4q, child number 0 ------------------------------------- select * from (select * from t  order by id1 desc) where rownum<=1 Plan hash value: 137725480 ----------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                     | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |         |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       4 | |*  1 |  COUNT STOPKEY                |         |      1 |        |       |            |          |      1 |00:00:00.01 |       4 | |   2 |   VIEW                        |         |      1 |      1 |    78 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       4 | |   3 |    TABLE ACCESS BY INDEX ROWID| T       |      1 |   1000K|   105M|     4   (0)| 00:00:01 |      1 |00:00:00.01 |       4 | |   4 |     INDEX FULL SCAN DESCENDING| I_T_ID1 |      1 |      1 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 | ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2 / T@SEL$2    4 - SEL$2 / T@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<=1) SCOTT@book> select * from (select * from t  order by id2 desc) where rownum<=1;        ID1        ID2 VC ---------- ---------- ----------------------------------------------------------------------------------------------------    1000000    1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  85pmrga0pr2jd, child number 0 ------------------------------------- select * from (select * from t  order by id2 desc) where rownum<=1 Plan hash value: 177228429 ----------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                     | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ----------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |         |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       4 | |*  1 |  COUNT STOPKEY                |         |      1 |        |       |            |          |      1 |00:00:00.01 |       4 | |   2 |   VIEW                        |         |      1 |      1 |    78 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       4 | |   3 |    TABLE ACCESS BY INDEX ROWID| T       |      1 |   1000K|   105M|     4   (0)| 00:00:01 |      1 |00:00:00.01 |       4 | |   4 |     INDEX FULL SCAN           | I_T_ID2 |      1 |      1 |       |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 | ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2 / T@SEL$2    4 - SEL$2 / T@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<=1) --//一样都是可以使用对应索引. --//很明显开发生搬硬套,使用降序索引毫无优势可言. --//应该有很好地使用降序索引优化的例子,一时半会我自己没有找到,那位给一些链接,我再看看....