[20200303]降序索引疑问5.txt
--//昨天在优化sql语句时遇到一条sql语句.也是使用order by desc.
--//突然想起一些细节我自己以前没有注意到.必须认真考虑.
--//语句大致如下,换成*,不然太长了.
SELECT * FROM (SELECT * FROM jcd this WHERE this.biaoshi = :1 ORDER BY this.jcjs_time desc ) WHERE rownum <= :2;
> @ bind_cap abnnf6rh06s6c ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ---- -------- ---------- ------------------- --------------- ------------ --------------
abnnf6rh06s6c 0 YES :1 1 22 2017-11-01 15:32:04 NUMBER 57
YES :2 2 22 2017-11-01 15:32:04 NUMBER 200
--//疑问1:如果像上面的jcjs_time,如果jcjs_time 有NULL,NULL值是否优先输出.因为NULL不管什么number还是字符.在oracle的编码是0xFF.
--//疑问2:也是我最关注的,如果查询写成如下:
SELECT * FROM (SELECT * FROM jcd this ORDER BY this.jcjs_time desc ) WHERE rownum <= :2;
--//建立jcjs_time的普通索引和降序索引,在有NULL值的情况下,降序索引是否有效.
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
create table t as select rownum id, trunc(dbms_random.value(1,100)) idx ,sysdate+rownum/1000-100 cr_date,rownum||lpad('x',100,'x') vc
from dual connect by level<=10e5-100;
insert into t select rownum+10e5-100 id, trunc(dbms_random.value(1,100)) idx ,NULL cr_date,rownum||lpad('x',100,'x') vc
from dual connect by level<=100;
commit ;
create index i_t_idx_cr_date on t(idx ,cr_date);
create index if_t_idx_cr_date on t(idx ,cr_date desc);
--//分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
2.测试:
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> SELECT * FROM (SELECT * FROM t WHERE idx = 42 ORDER BY cr_date desc ) WHERE rownum <=5;
ID IDX CR_DATE VC
------ --- ------------------- ----------------------------------------------------------------------------------------------------------
999913 42 13xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999884 42 2022-08-20 06:41:07 999884xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999698 42 2022-08-20 02:13:16 999698xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999543 42 2022-08-19 22:30:04 999543xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999382 42 2022-08-19 18:38:14 999382xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--//可以发现NULL在第1个。
Plan hash value: 2332835607
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 5 |00:00:00.01 | 9 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 9 |
| 2 | VIEW | | 1 | 6 | 642 | 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 10101 | 1213K| 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_IDX_CR_DATE | 1 | 6 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------------------------------
SCOTT@book> SELECT * FROM (SELECT /*+ index(t IF_T_IDX_CR_DATE )*/ * FROM t WHERE idx = 42 ORDER BY cr_date desc ) WHERE rownum <= 5;
ID IDX CR_DATE VC
------ --- ------------------- ----------------------------------------------------------------------------------------------------------
999913 42 13xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999884 42 2022-08-20 06:41:07 999884xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999698 42 2022-08-20 02:13:16 999698xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999543 42 2022-08-19 22:30:04 999543xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999382 42 2022-08-19 18:38:14 999382xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Plan hash value: 313436017
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 5 |00:00:00.01 | 9 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 9 |
| 2 | VIEW | | 1 | 6 | 642 | 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10101 | 1213K| 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
|* 4 | INDEX RANGE SCAN | IF_T_IDX_CR_DATE | 1 | 6 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------------------------------
--//可以发现排序时NULL在最前面。
3.测试2
drop index i_t_idx_cr_date ;
drop index if_t_idx_cr_date;
create index i_t_cr_date on t(cr_date);
create index if_t_cr_date on t(cr_date desc);
--//分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
SCOTT@book> select index_name,index_type,blevel,leaf_blocks,distinct_keys,num_rows from dba_indexes where owner=user and table_name='T';
INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ --------------------------- ---------- ----------- ------------- ----------
I_T_CR_DATE NORMAL 2 2653 999900 999900
IF_T_CR_DATE FUNCTION-BASED NORMAL 2 2793 999901 1000000
--//从NUM_ROWS，DISTINCT_KEYS就可以看出降序索引是包含NULL的。
SCOTT@book> SELECT * FROM (SELECT * FROM t ORDER BY cr_date desc ) WHERE rownum <= 5;
ID IDX CR_DATE VC
------ --- ------------------- -----------------------------------------------------------------------------------------------------
999901 77 1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999905 5 5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999904 47 4xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999903 51 3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999902 19 2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Plan hash value: 3299198703
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 32529 (100)| | 5 |00:00:00.68 | 17897 | 16777 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.68 | 17897 | 16777 | | | |
| 2 | VIEW | | 1 | 1000K| 102M| | 32529 (1)| 00:06:31 | 5 |00:00:00.68 | 17897 | 16777 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 117M| 130M| 32529 (1)| 00:06:31 | 5 |00:00:00.68 | 17897 | 16777 | 1683K| 633K| 1495K (0)|
| 4 | TABLE ACCESS FULL | T | 1 | 1000K| 117M| | 4933 (1)| 00:01:00 | 1000K|00:00:00.23 | 17897 | 16777 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//执行计划选择全表扫描。并没有使用索引，使用普通索引可能不行，因为cr_date有NULL值。而降序索引包含NULL的，也不会使用。
--//加入提示看看。
SELECT * FROM (SELECT /*+ index(t IF_T_CR_DATE )*/ * FROM t ORDER BY cr_date desc ) WHERE rownum <= 5;
Plan hash value: 3299198703
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 32529 (100)| | 5 |00:00:00.65 | 17897 | 16878 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.65 | 17897 | 16878 | | | |
| 2 | VIEW | | 1 | 1000K| 102M| | 32529 (1)| 00:06:31 | 5 |00:00:00.65 | 17897 | 16878 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 117M| 130M| 32529 (1)| 00:06:31 | 5 |00:00:00.65 | 17897 | 16878 | 1683K| 633K| 1495K (0)|
| 4 | TABLE ACCESS FULL | T | 1 | 1000K| 117M| | 4933 (1)| 00:01:00 | 1000K|00:00:00.22 | 17897 | 16878 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//根本没有理会提示。
4.加入过滤排除NULL值看看。
SCOTT@book> SELECT * FROM (SELECT * FROM t where cr_date is not null ORDER BY cr_date desc ) WHERE rownum <= 5;
ID IDX CR_DATE VC
------ --- ------------------- ----------------------------------------------------------------------------------------------------------
999900 88 2022-08-20 07:04:09 999900xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999899 23 2022-08-20 07:02:43 999899xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999898 89 2022-08-20 07:01:16 999898xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999897 24 2022-08-20 06:59:50 999897xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999896 98 2022-08-20 06:58:23 999896xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Plan hash value: 3230194292
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 5 |00:00:00.01 | 6 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 6 | 1 |
| 2 | VIEW | | 1 | 5 | 535 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 999K| 117M| 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | 1 |
|* 4 | INDEX FULL SCAN DESCENDING| I_T_CR_DATE | 1 | 5 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------------
--//可以使用普通索引。
SCOTT@book> SELECT * FROM (SELECT /*+ index(t IF_T_CR_DATE )*/ * FROM t where cr_date is not null ORDER BY cr_date desc ) WHERE rownum <= 5;
ID IDX CR_DATE VC
------ --- ------------------- ----------------------------------------------------------------------------------------------------------
999900 88 2022-08-20 07:04:09 999900xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999899 23 2022-08-20 07:02:43 999899xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999898 89 2022-08-20 07:01:16 999898xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999897 24 2022-08-20 06:59:50 999897xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999896 98 2022-08-20 06:58:23 999896xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Plan hash value: 3299198703
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 32526 (100)| | 5 |00:00:00.68 | 17897 | 16887 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.68 | 17897 | 16887 | | | |
| 2 | VIEW | | 1 | 999K| 102M| | 32526 (1)| 00:06:31 | 5 |00:00:00.68 | 17897 | 16887 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 999K| 117M| 130M| 32526 (1)| 00:06:31 | 5 |00:00:00.68 | 17897 | 16887 | 1683K| 633K| 1495K (0)|
|* 4 | TABLE ACCESS FULL | T | 1 | 999K| 117M| | 4934 (1)| 00:01:00 | 999K|00:00:00.23 | 17897 | 16887 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//提示无效，并且还导致执行计划选择全表扫描，oracle这优化器优化方式也真奇怪了。
--//从这里也可以看出oracle的降序索引"很傻".更加提示降序索引不能乱用，适用的场景很少。
5.总结：
1.NULL在排序desc没有指定的情况下优先输出。
2.降序索引的适用范围很窄。
6.补充测试：
--//NULLS last 或者NULLs first的情况。
drop index i_t_cr_date;
drop index if_t_cr_date;
create index i_t_idx_cr_date on t(idx ,cr_date);
create index if_t_idx_cr_date on t(idx ,cr_date desc);
SCOTT@book> select index_name,index_type,blevel,leaf_blocks,distinct_keys,num_rows from dba_indexes where owner=user and table_name='T';
INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ --------------------------- ---------- ----------- ------------- ----------
I_T_IDX_CR_DATE NORMAL 2 3068 999964 1000000
IF_T_IDX_CR_DATE FUNCTION-BASED NORMAL 2 3213 999964 1000000
SCOTT@book> SELECT * FROM (SELECT * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS last ) WHERE rownum <=5;
ID IDX CR_DATE VC
------ --- ------------------- ----------------------------------------------------------------------------------------------------------
999884 42 2022-08-20 06:41:07 999884xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999698 42 2022-08-20 02:13:16 999698xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999543 42 2022-08-19 22:30:04 999543xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999382 42 2022-08-19 18:38:14 999382xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
999340 42 2022-08-19 17:37:45 999340xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 | | | | 5216 (100)| | 5 |00:00:00.14 | 17897 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.14 | 17897 | | | |
| 2 | VIEW | | 1 | 10101 | 1055K| | 5216 (1)| 00:01:03 | 5 |00:00:00.14 | 17897 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 10101 | 1213K| 1360K| 5216 (1)| 00:01:03 | 5 |00:00:00.14 | 17897 | 22528 | 22528 |20480 (0)|
|* 4 | TABLE ACCESS FULL | T | 1 | 10101 | 1213K| | 4933 (1)| 00:01:00 | 10225 |00:00:00.13 | 17897 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--//使用全表扫描。
--//加入提示后：
SELECT * FROM (SELECT /*+ index(t I_T_IDX_CR_DATE) */ * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS last ) WHERE rownum <=5;
Plan hash value: 1786862719
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 8061 (100)| | 5 |00:00:00.04 | 7806 | 35 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.04 | 7806 | 35 | | | |
| 2 | VIEW | | 1 | 10101 | 1055K| | 8061 (1)| 00:01:37 | 5 |00:00:00.04 | 7806 | 35 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 10101 | 1213K| 1360K| 8061 (1)| 00:01:37 | 5 |00:00:00.04 | 7806 | 35 | 22528 | 22528 |20480 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10101 | 1213K| | 7779 (1)| 00:01:34 | 10225 |00:00:00.03 | 7806 | 35 | | | |
|* 5 | INDEX RANGE SCAN | I_T_IDX_CR_DATE | 1 | 10101 | | | 33 (0)| 00:00:01 | 10225 |00:00:00.01 | 35 | 34 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//实际上扫描索引(idx=42的全部)，cost比前面全表扫描要高。
SELECT * FROM (SELECT /*+ index(t IF_T_IDX_CR_DATE) */ * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS last ) WHERE rownum <=5;
Plan hash value: 1966750027
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 8063 (100)| | 5 |00:00:00.03 | 7807 | 35 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.03 | 7807 | 35 | | | |
| 2 | VIEW | | 1 | 10101 | 1055K| | 8063 (1)| 00:01:37 | 5 |00:00:00.03 | 7807 | 35 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 10101 | 1213K| 1360K| 8063 (1)| 00:01:37 | 5 |00:00:00.03 | 7807 | 35 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10101 | 1213K| | 7781 (1)| 00:01:34 | 10225 |00:00:00.02 | 7807 | 35 | | | |
|* 5 | INDEX RANGE SCAN | IF_T_IDX_CR_DATE | 1 | 10101 | | | 35 (0)| 00:00:01 | 10225 |00:00:00.01 | 36 | 35 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以使用，但是你对比前面的情况，可以发现使用降序索引毫无优势。
--//使用NULLS first设置实际上对于desc是缺省设置。
SELECT * FROM (SELECT * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS first ) WHERE rownum<=5;
Plan hash value: 2332835607
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 5 |00:00:00.01 | 9 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 9 |
| 2 | VIEW | | 1 | 6 | 642 | 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 10101 | 1213K| 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_IDX_CR_DATE | 1 | 6 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------------------------------
--//即使提示看看。
SELECT * FROM (SELECT /*+ index(t I_T_IDX_CR_DATE) */ * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS first ) WHERE rownum <=5;
Plan hash value: 1786862719
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 8061 (100)| | 5 |00:00:00.06 | 7806 | 1613 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.06 | 7806 | 1613 | | | |
| 2 | VIEW | | 1 | 10101 | 1055K| | 8061 (1)| 00:01:37 | 5 |00:00:00.06 | 7806 | 1613 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 10101 | 1213K| 1360K| 8061 (1)| 00:01:37 | 5 |00:00:00.06 | 7806 | 1613 | 22528 | 22528 |20480 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10101 | 1213K| | 7779 (1)| 00:01:34 | 10225 |00:00:00.05 | 7806 | 1613 | | | |
|* 5 | INDEX RANGE SCAN | I_T_IDX_CR_DATE | 1 | 10101 | | | 33 (0)| 00:00:01 | 10225 |00:00:00.01 | 35 | 32 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//注意看不是INDEX RANGE SCAN DESCENDING，感觉oracle优化器还是不够智能。实际上正确的提示是：INDEX_DESC
SELECT * FROM (SELECT /*+ index_desc(t I_T_IDX_CR_DATE) */ * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS first ) WHERE rownum <=5;
Plan hash value: 2332835607
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 5 |00:00:00.01 | 9 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 9 |
| 2 | VIEW | | 1 | 6 | 642 | 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 6 | 738 | 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_IDX_CR_DATE | 1 | 10101 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM (SELECT /*+ index(t IF_T_IDX_CR_DATE) */ * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS first ) WHERE rownum <=5;
Plan hash value: 313436017
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 5 |00:00:00.01 | 9 | 3 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 9 | 3 |
| 2 | VIEW | | 1 | 6 | 642 | 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 | 3 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 10101 | 1213K| 8 (0)| 00:00:01 | 5 |00:00:00.01 | 9 | 3 |
|* 4 | INDEX RANGE SCAN | IF_T_IDX_CR_DATE | 1 | 6 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//再补充index_desc的情况：
SELECT * FROM (SELECT /*+ index_desc(t I_T_IDX_CR_DATE) */ * FROM t WHERE idx = 42 ORDER BY cr_date desc NULLS last ) WHERE rownum <=5;
Plan hash value: 669288848
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | | 8061 (100)| | 5 |00:00:00.03 | 7806 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.03 | 7806 | | | |
| 2 | VIEW | | 1 | 10101 | 1055K| | 8061 (1)| 00:01:37 | 5 |00:00:00.03 | 7806 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 10101 | 1213K| 1360K| 8061 (1)| 00:01:37 | 5 |00:00:00.03 | 7806 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | T | 1 | 10101 | 1213K| | 7779 (1)| 00:01:34 | 10225 |00:00:00.02 | 7806 | | | |
|* 5 | INDEX RANGE SCAN DESCENDING| I_T_IDX_CR_DATE | 1 | 10101 | | | 33 (0)| 00:00:01 | 10225 |00:00:00.01 | 35 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//提示有效，但是无法充分利用降序的特性优化语句。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2678406/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
