在Oracle中，降序索引和升序索引分别是什么？

DB宝 2019-05-09

5367

♣题目部分在Oracle中，降序索引和升序索引分别是什么？

     
♣答案部分

对于升序索引（Ascending Indexes），数据库按升序排列的顺序存储数据。索引默认按照升序存储列值。默认情况下，字符数据按每个字节中包含的二进制值排序，数值数据按从小到大排序，日期数据从早到晚排序。
降序索引（Descending Indexes）将存储在一个特定的列或多列中的数据按降序排序。创建降序索引时使用DESC关键字，如下所示：
1CREATE INDEX IND_DESC ON TESTDESC(A DESC,B ASC);

需要注意的是，降序索引在DBA_INDEXES的INDEX_TYPE列表现为FUNCTION-BASED即函数索引，但是在DBA_IND_EXPRESSIONS不能体现其升序或降序，只能通过视图DBA_IND_COLUMNS的DESCEND列来查询，如下所示：
先创建表和索引：
1CREATE TABLE XT_DESC_LHR AS SELECT * FROM DBA_OBJECTS;
2CREATE INDEX IND_DESC_LHR ON XT_DESC_LHR(OBJECT_ID DESC,OBJECT_NAME ASC);
3CREATE INDEX IND_DESC_LHR2 ON XT_DESC_LHR(OBJECT_NAME DESC); 
4CREATE INDEX IND_DESC_LHR3 ON XT_DESC_LHR(OBJECT_type ASC);


查询索引：
 1SYS@orclasm > SELECT D.INDEX_NAME,D.INDEX_TYPE FROM DBA_INDEXES D WHERE   D.INDEX_NAME LIKE  'IND_DESC_LHR%';
 2INDEX_NAME                     INDEX_TYPE
 3------------------------------ ---------------------------
 4IND_DESC_LHR                   FUNCTION-BASED NORMAL
 5IND_DESC_LHR2                  FUNCTION-BASED NORMAL
 6IND_DESC_LHR3                  NORMAL
 7SYS@orclasm > SET LINE 9999
 8SYS@orclasm > SELECT D.INDEX_NAME,D.COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS D WHERE D.INDEX_NAME LIKE 'IND_DESC_LHR%' ;
 9INDEX_NAME                     COLUMN_EXPRESSION
10------------------------------ -------------------------
11IND_DESC_LHR                   "OBJECT_ID"
12IND_DESC_LHR2                  "OBJECT_NAME"
13SYS@orclasm > COL COLUMN_NAME FORMAT A15
14SYS@orclasm > SELECT d.INDEX_NAME,d.COLUMN_NAME,d.COLUMN_POSITION,d.DESCEND FROM DBA_IND_COLUMNS D WHERE D.INDEX_NAME  LIKE  'IND_DESC_LHR%' ORDER BY d.INDEX_NAME,d.COLUMN_POSITION; 
15INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION DESC
16------------------------------ --------------- --------------- ----
17IND_DESC_LHR                   SYS_NC00016$                  1 DESC
18IND_DESC_LHR                   OBJECT_NAME                   2 ASC
19IND_DESC_LHR2                  SYS_NC00017$                  1 DESC
20IND_DESC_LHR3                  OBJECT_TYPE                   1 ASC
21SYS@orclasm > SELECT COLUMN_NAME,DATA_TYPE,DATA_DEFAULT FROM DBA_TAB_COLS WHERE OWNER='LHR' AND TABLE_NAME='XT_DESC_LHR' AND COLUMN_NAME='SYS_NC00016$';
22COLUMN_NAME                    DATA_TYPE              DATA_DEFAULT
23------------------------------ ---------------------- -----------------
24SYS_NC00016$                   RAW                    "OBJECT_ID"
25LHR@orclasm > SELECT * FROM XT_DESC_LHR t WHERE t.object_name='LHR' AND T.OBJECT_ID=1 ORDER BY OBJECT_ID DESC,OBJECT_NAME ASC;
26no rows selected
27Execution Plan
28----------------------------------------------------------
29Plan hash value: 902722624
30--------------------------------------------------------------------------------------------
31| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
32--------------------------------------------------------------------------------------------
33|   0 | SELECT STATEMENT            |              |     8 |  1656 |     2   (0)| 00:00:01 |
34|   1 |  TABLE ACCESS BY INDEX ROWID| XT_DESC_LHR  |     8 |  1656 |     2   (0)| 00:00:01 |
35|*  2 |   INDEX RANGE SCAN          | IND_DESC_LHR |     1 |       |     1   (0)| 00:00:01 |
36--------------------------------------------------------------------------------------------
37Predicate Information (identified by operation id):
38---------------------------------------------------
39   2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3EFDFF')  AND
40              "T"."OBJECT_NAME"='LHR')
41       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=1)
42Note
43-----
44   - dynamic sampling used for this statement (level=2)
45Statistics
46----------------------------------------------------------
47          0  recursive calls
48          0  db block gets
49          2  consistent gets
50          0  physical reads
51          0  redo size
52       1343  bytes sent via SQL*Net to client
53        508  bytes received via SQL*Net from client
54          1  SQL*Net roundtrips to/from client
55          0  sorts (memory)
56          0  sorts (disk)
57          0  rows processed

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。