oracle 降序索引

创建数据：

SQL> create table tb_desc_index  2  (  3      user_year int not null,  4      user_month int not null,  5      user_date int not null,  6      user_name varchar2(20) null  7  );表已创建。SQL> DECLARE  2    V_DATE INT;  3    V_MONTH INT;  4    V_YEAR INT;  5  BEGIN  6    FOR I IN 1..1000 LOOP  7      V_DATE := I mod 30;  8      V_DATE := V_DATE + 1;  9      V_MONTH := I mod 12; 10      V_MONTH := V_MONTH + 1; 11      V_YEAR := 2010 + I / 100; 12      INSERT INTO tb_desc_index VALUES (V_YEAR, V_MONTH, V_DATE, 'user_' || I); 13    END LOOP; 14    COMMIT; 15  END; 16  /PL/SQL 过程已成功完成。

创建降序索引：

SQL> create index idx_desc_index on tb_desc_index(user_year desc, user_month desc, user_date desc);索引已创建。

查询并查看执行计划：

SQL> set autotrace traceonlySQL> set linesize 120SQL> select * from tb_desc_index  2  where user_year <= 2014 and user_month <= 7 and user_date <= 20  3  order by user_year desc, user_month desc, user_date desc;已选择181行。执行计划----------------------------------------------------------Plan hash value: 4208050085----------------------------------------------------------------------------------------------| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                |   176 |  3344 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| TB_DESC_INDEX  |   176 |  3344 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_DESC_INDEX |     1 |       |     2   (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access(SYS_OP_DESCEND("USER_YEAR")>=HEXTORAW('3DEAF0FF')  AND              SYS_OP_DESCEND("USER_MONTH")>=HEXTORAW('3EF7FF')  AND              SYS_OP_DESCEND("USER_DATE")>=HEXTORAW('3EEAFF')  AND SYS_OP_DESCEND("USER_YEAR") IS              NOT NULL)       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("USER_YEAR"))<=2014 AND              SYS_OP_UNDESCEND(SYS_OP_DESCEND("USER_MONTH"))<=7 AND              SYS_OP_UNDESCEND(SYS_OP_DESCEND("USER_DATE"))<=20 AND              SYS_OP_DESCEND("USER_YEAR")>=HEXTORAW('3DEAF0FF')  AND              SYS_OP_DESCEND("USER_MONTH")>=HEXTORAW('3EF7FF')  AND              SYS_OP_DESCEND("USER_DATE")>=HEXTORAW('3EEAFF') )统计信息----------------------------------------------------------          0  recursive calls          0  db block gets         61  consistent gets          0  physical reads          0  redo size       5071  bytes sent via SQL*Net to client        517  bytes received via SQL*Net from client         14  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)        181  rows processedSQL>

看看普通索引会怎么样：

SQL> select * from tb_desc_index  2  where user_year <= 2014 and user_month <= 7 and user_date <= 20  3  order by user_year desc, user_month desc, user_date desc;已选择181行。执行计划----------------------------------------------------------Plan hash value: 771444667------------------------------------------------------------------------------------| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |               |   176 |  3344 |     4  (25)| 00:00:01 ||   1 |  SORT ORDER BY     |               |   176 |  3344 |     4  (25)| 00:00:01 ||*  2 |   TABLE ACCESS FULL| TB_DESC_INDEX |   176 |  3344 |     3   (0)| 00:00:01 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("USER_YEAR"<=2014 AND "USER_MONTH"<=7 AND "USER_DATE"<=20)统计信息----------------------------------------------------------          1  recursive calls          0  db block gets          7  consistent gets          0  physical reads          0  redo size       5071  bytes sent via SQL*Net to client        517  bytes received via SQL*Net from client         14  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)        181  rows processedSQL>

做这次实验时，一开始使用的SQL是：

SQL> select user_year from tb_desc_index  2  where user_year in (2010, 2011, 2012)  3  order by user_year desc;select user_year from tb_desc_index*第 1 行出现错误:ORA-03113: 通信通道的文件结束

查原因，一直没查到。

好像直接select字段名就不行。

版本10g。也许是oracle的一个bug。