[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)
--//一样都是可以使用对应索引.
--//很明显开发生搬硬套,使用降序索引毫无优势可言.
--//应该有很好地使用降序索引优化的例子,一时半会我自己没有找到,那位给一些链接,我再看看....
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2667573/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
