【DB笔试面试559】在Oracle中，降序索引和升序索引分别是什么？

CREATE INDEX IND_DESC ON TESTDESC(A DESC,B ASC);

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