[20200303]降序索引疑问5.txt

来源：这里教程网时间：2026-03-03 15:12:45 作者：

[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 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//提示有效，但是无法充分利用降序的特性优化语句。

编辑推荐：

返回列表

[20200303]降序索引疑问5.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推