DESCENDING 인덱스는 일부 인덱스 칼럼이 내림차순으로 정렬된 인덱스로 오름차순과 내림차순이 혼재된 정렬을 처리하기 위한 용도로 사용됩니다.
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다. t1_x1 인덱스는 c2 칼럼이 내림차순으로 정렬된 DESCENDING 인덱스입니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT CEIL (ROWNUM / 2) AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10');
CREATE INDEX t1_x1 ON t1 (c1, c2 DESC);
DESCENDING 인덱스는 FBI로 생성됩니다. SYS_NC00003$ 칼럼은 t1 테이블에 암시적으로 생성된 Virtual 칼럼입니다. 내림차순으로 정렬된 인덱스 칼럼은 *_IND_COLUMNS 뷰의 descend 값이 DESC 표시됩니다.
-- 2-1
SELECT index_type
FROM user_indexes
WHERE index_name = 'T1_X1';
INDEX_TYPE
---------------------
FUNCTION-BASED NORMAL
1개의 행이 선택되었습니다.
-- 2-2
SELECT column_name, column_position, descend
FROM user_ind_columns
WHERE index_name = 'T1_X1';
COLUMN_NAME COLUMN_POSITION DESCEND
------------ --------------- -------
C1 1 ASC
SYS_NC00003$ 2 DESC
2 행이 선택되었습니다.
-- 2-3
SELECT column_expression, column_position
FROM user_ind_expressions
WHERE index_name = 'T1_X1';
COLUMN_EXPRESSION COLUMN_POSITION
----------------- ---------------
"C2" 2
1개의 행이 선택되었습니다.
-- 2-4
SELECT column_name, data_type, data_length, data_default, hidden_column, virtual_column, user_generated
FROM user_tab_cols
WHERE table_name = 'T1';
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT HIDDEN_COLUMN VIRTUAL_COLUMN USER_GENERATED
------------ --------- ----------- ------------ ------------- -------------- --------------
C1 NUMBER 22 NO NO YES
C2 NUMBER 22 NO NO YES
SYS_NC00003$ RAW 34 "C2" YES YES NO
3 행이 선택되었습니다.
아래 3-1번 쿼리는 인덱스를 오름차순으로 읽어 c1 칼럼은 오름차순, c2 칼럼은 내림차순으로 정렬되었습니다. 3-2번 쿼리는 인덱스를 내림차순으로 읽어 c1 칼럼은 내림차순, c2 칼럼은 오름차순으로 정렬되었습니다.
-- 3-1
SELECT /*+ INDEX(T1 T1_X1) */ * FROM t1 WHERE c1 > 0;
C1 C2
-- --
1 2
1 1
2 4
2 3
...
10 행이 선택되었습니다.
-- 3-2
SELECT /*+ INDEX_DESC(T1 T1_X1) */ * FROM t1 WHERE c1 > 0;
C1 C2
-- --
5 9
5 10
4 7
4 8
...
10 행이 선택되었습니다.
아래 4-1번 쿼리는 내림차순으로 정렬된 c2 칼럼에 상수를 사용합니다. Predicate Information 항목의 access 조건에서 c2 칼럼에 SYS_OP_DESCEND 함수, 상수값에 HEXTORAW 함수가 사용되었고, filter 조건에서 SYS_OP_UNDESCEND 함수가 사용되었습니다. 4-2번 쿼리는 c2 칼럼에 상수를 사용합니다. Predicate Information 항목의 access 조건에서 바인드 변수에 SYS_OP_DESCEND 함수가 사용된 것을 확인할 수 있습니다.
-- 4-1
SELECT /*+ INDEX(T1 T1_X1) */ * FROM t1 WHERE c1 = 1 AND c2 > 0;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| T1_X1 |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=1 AND SYS_OP_DESCEND("C2")<HEXTORAW('7FFF'))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>0)
-- 4-2
VAR b1 NUMBER
EXEC :b1 := 0
SELECT /*+ INDEX(T1 T1_X1) */ * FROM t1 WHERE c1 = 1 AND c2 > :b1;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| T1_X1 |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=1 AND SYS_OP_DESCEND("C2")<SYS_OP_DESCEND(TO_NUMBER(:B1)))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>TO_NUMBER(:B1))
아래 쿼리에서 SYS_OP_DESCEND 함수와 SYS_OP_UNDESCEND 함수의 결과를 확인할 수 있습니다. SYS_OP_DESCEND 함수는 입력 값을 내림차순으로 정렬한 RAW 값을 반환하고, SYS_OP_UNDESCEND 함수는 RAW 값의 원래 값을 반환합니다.
-- 5
SELECT c1, c2
, SYS_OP_DESCEND (c2) AS descend
, SYS_OP_UNDESCEND (SYS_OP_DESCEND (c2)) AS undescend
FROM t1;
C1 C2 DESCEND UNDESCEND
-- -- ------- ---------
1 1 3EFDFF 1
1 2 3EFCFF 2
2 3 3EFBFF 3
2 4 3EFAFF 4
3 5 3EF9FF 5
3 6 3EF8FF 6
4 7 3EF7FF 7
4 8 3EF6FF 8
5 9 3EF5FF 9
5 10 3EF4FF 10
10 행이 선택되었습니다.
아래 6-1번 쿼리는 c2 칼럼을 오름차순으로 정렬하여 t1_x1 인덱스가 내림차순으로 스캔되었습니다. 반대로 6-2번 쿼리는 c2 칼럼을 내림차순으로 정렬하여 t1_x1 인덱스가 오름차순으로 스캔되었습니다.
-- 6-1
SELECT * FROM t1 WHERE c1 = 1 ORDER BY c2;
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN DESCENDING| T1_X1 |
---------------------------------------------
-- 6-2
SELECT * FROM t1 WHERE c1 = 1 ORDER BY c2 DESC;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| T1_X1 |
----------------------------------
아래와 같이 일반 인덱스를 사용하면 정렬 순서와 인덱스 스캔 순서가 동일합니다.
-- 7-1
CREATE INDEX t1_x2 ON t1 (c1, c2);
-- 7-2
SELECT * FROM t1 WHERE c1 = 1 ORDER BY c2;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| T1_X2 |
----------------------------------
-- 7-3
SELECT * FROM t1 WHERE c1 = 1 ORDER BY c2 DESC;
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN DESCENDING| T1_X2 |
---------------------------------------------
관련 링크
- Oracle Scratchpad - Descending Indexes
- Richard Foote's Oracle Blog - Descending Indexes Solution (Yellow Submarine)