Top-N 쿼리 개선 사례

특이한 Ton-N 쿼리 개선 사례를 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, 'X' as c2 FROM XMLTABLE ('1 to 100');
CREATE INDEX t1_x1 ON t1 (c1);

아래 쿼리는 v_rn 바인드 변수에 입력된 인수가 -1이면 전체 행, -1이 아니면 v_rn개의 행을 조회한다. 제대로 동작할 것 같지만 부분범위 처리가 되지 않을 것을 확인할 수 있다. ROWNUM <= DECODE (:v_rn, -1, ROWNUM, :v_rn) 조건이 ROWNUM <= ROWNUM 조건으로 해석되어 인덱스 스캔을 멈출 수 없기 때문이다.

-- 2
VARIABLE v_rn NUMBER;
EXEC :v_rn := 10;

SELECT *
  FROM (SELECT * FROM t1 WHERE c1 > 0 ORDER BY c1)
 WHERE ROWNUM <= DECODE (:v_rn, -1, ROWNUM, :v_rn);

---------------------------------------------------------
| Id  | Operation                      | Name  | A-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT               |       |     10 |
|   1 |  COUNT                         |       |     10 |
|*  2 |   FILTER                       |       |     10 |
|   3 |    VIEW                        |       |    100 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |    100 |
|*  5 |      INDEX RANGE SCAN          | T1_X1 |    100 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=DECODE(:V_RN,(-1),ROWNUM,:V_RN))

DECODE 함수에 ROWNUM 슈도 칼럼 대신 상수(9E9)를 사용하면 부분범위 처리가 동작하는 것을 확인할 수 있다.

-- 3
SELECT *
  FROM (SELECT * FROM t1 WHERE c1 > 0 ORDER BY c1)
 WHERE ROWNUM <= DECODE (:v_rn, -1, 9E9, :v_rn);

--------------------------------------------------------
| Id  | Operation                     | Name  | A-Rows |
--------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     10 |
|*  1 |  COUNT STOPKEY                |       |     10 |
|   2 |   VIEW                        |       |     10 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |     10 |
|*  4 |     INDEX RANGE SCAN          | T1_X1 |     10 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=DECODE(:V_RN,(-1),9000000000,:V_RN))