【DB笔试面试559】在Oracle中，降序索引和升序索引分别是什么？
- 2019 年 10 月 10 日
- 笔记
♣
题目部分
在Oracle中，降序索引和升序索引分别是什么？
♣
答案部分
对于升序索引（Ascending Indexes），数据库按升序排列的顺序存储数据。索引默认按照升序存储列值。默认情况下，字符数据按每个字节中包含的二进制值排序，数值数据按从小到大排序，日期数据从早到晚排序。
降序索引（Descending Indexes）将存储在一个特定的列或多列中的数据按降序排序。创建降序索引时使用DESC关键字，如下所示：
CREATE INDEX IND_DESC ON TESTDESC(A DESC,B ASC);
需要注意的是，降序索引在DBA_INDEXES的INDEX_TYPE列表现为FUNCTION-BASED即函数索引，但是在DBA_IND_EXPRESSIONS不能体现其升序或降序，只能通过视图DBA_IND_COLUMNS的DESCEND列来查询，如下所示：
先创建表和索引：
CREATE TABLE XT_DESC_LHR AS SELECT * FROM DBA_OBJECTS; CREATE INDEX IND_DESC_LHR ON XT_DESC_LHR(OBJECT_ID DESC,OBJECT_NAME ASC); CREATE INDEX IND_DESC_LHR2 ON XT_DESC_LHR(OBJECT_NAME DESC); CREATE INDEX IND_DESC_LHR3 ON XT_DESC_LHR(OBJECT_type ASC);
查询索引：
SYS@orclasm > SELECT D.INDEX_NAME,D.INDEX_TYPE FROM DBA_INDEXES D WHERE D.INDEX_NAME LIKE 'IND_DESC_LHR%'; INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- IND_DESC_LHR FUNCTION-BASED NORMAL IND_DESC_LHR2 FUNCTION-BASED NORMAL IND_DESC_LHR3 NORMAL SYS@orclasm > SET LINE 9999 SYS@orclasm > SELECT D.INDEX_NAME,D.COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS D WHERE D.INDEX_NAME LIKE 'IND_DESC_LHR%' ; INDEX_NAME COLUMN_EXPRESSION ------------------------------ ------------------------- IND_DESC_LHR "OBJECT_ID" IND_DESC_LHR2 "OBJECT_NAME" SYS@orclasm > COL COLUMN_NAME FORMAT A15 SYS@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; INDEX_NAME COLUMN_NAME COLUMN_POSITION DESC ------------------------------ --------------- --------------- ---- IND_DESC_LHR SYS_NC00016$ 1 DESC IND_DESC_LHR OBJECT_NAME 2 ASC IND_DESC_LHR2 SYS_NC00017$ 1 DESC IND_DESC_LHR3 OBJECT_TYPE 1 ASC SYS@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$'; COLUMN_NAME DATA_TYPE DATA_DEFAULT ------------------------------ ---------------------- ----------------- SYS_NC00016$ RAW "OBJECT_ID" LHR@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; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 902722624 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1656 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| XT_DESC_LHR | 8 | 1656 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_DESC_LHR | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3EFDFF') AND "T"."OBJECT_NAME"='LHR') filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=1) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 1343 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
