创建数据:
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 traceonly
SQL> set linesize 120
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: 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 processed
SQL>
看看普通索引会怎么样:
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 processed
SQL>
做这次实验时,一开始使用的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。