oracle 降序索引

最新推荐文章于 2022-06-01 19:10:35 发布

feier7501

最新推荐文章于 2022-06-01 19:10:35 发布

阅读量4.2k

点赞数 1

分类专栏： oracle

本文链接：https://blog.csdn.net/feier7501/article/details/23711229

版权

oracle 专栏收录该内容

103 篇文章 2 订阅

订阅专栏

创建数据：

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。