[20200303]降序索引疑問5.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2678406/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191209]降序索引疑問.txt索引
- [20190910]關於降序索引問題5.txt索引
- [20191209]降序索引疑問2.txt索引
- [20191210]降序索引疑問3.txt索引
- [20191218]降序索引疑問4.txt索引
- [20190509]rman備份的疑問5.txt
- [20181123]關於降序索引問題.txt索引
- 不能建立降序索引的問題的解決索引
- [20181124]關於降序索引問題4.txt索引
- [20181124]關於降序索引問題3.txt索引
- [20181124]關於降序索引問題2.txt索引
- [20231116]降序索引取最大值.txt索引
- [20191219]降序索引與取最大值.txt索引
- 有些疑問
- 我的疑問
- pycharm 小疑問PyCharm
- 【疑問】記錄
- 做學問要在不疑之處有疑
- locust 使用的疑問
- 帶著疑問看故事
- 面試疑難問題面試
- Solidity學習疑問總結Solid
- 關於 dingo API 的疑問GoAPI
- [20190411]linux stat 命令疑問.txtLinux
- 安卓串列埠通訊疑問安卓串列埠
- Android 編譯打包的那些疑問Android編譯
- 反射型 XSS 疑問及延伸（CSRF）反射
- [20190401]那個更快的疑問.txt
- 工作疑難問題解決4例
- [20191213]不完全恢復疑問.txt
- Vue學習遇到疑問的總結Vue
- RESTful設計中的常見疑問REST
- Android學習: 疑難問題總結Android
- java——ArrayList中contains（）方法中的疑問JavaAI
- [20210914]探究mutex的值 5.txtMutex
- [20200401]優化的困惑5.txt優化
- [20210218]shared latch spin count 5.txt
- 關於租用香港伺服器疑問解答伺服器