ROWNUM = 1 패턴

ROWNUM = 1 패턴은 cursor_sharing 파라미터 설정에 따라 성능 저하가 발생할 수 있다는 이유로 ROWNUM <= 1로 수정하곤 했다. 관련 내용을 살펴보자.

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

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

아래 쿼리는 ROWNUM 슈도 칼럼에 <= 연산자와 바인드 변수를 사용했다. 예상 실행 계획에서 COUNT STOPKEY 오퍼레이션을 확인할 수 있다.

-- 2-1
EXPLAIN PLAN FOR
SELECT *
  FROM (SELECT * FROM t1 WHERE c1 >= 1 ORDER BY c1 DESC)
 WHERE ROWNUM <= :v1;

----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |    82 |  5330 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |       |       |       |            |          | - !
|   2 |   VIEW                         |       |    82 |  5330 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T1    |    82 |  5330 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| T1_X1 |    15 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

아래 쿼리는 ROWNUM 슈도 칼럼에 = 연산자와 바인드 변수를 사용했다. 예상 실행 계획에서 COUNT STOPKEY가 동작하지 않는 것을 확인할 수 있다. 바인드 변수에 1이 아닌 값을 입력하면 결과가 반환되지 않기 때문으로 보인다.

-- 2-2
EXPLAIN PLAN FOR
SELECT *
  FROM (SELECT * FROM t1 WHERE c1 >= 1 ORDER BY c1 DESC)
 WHERE ROWNUM = :v1;

-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |    82 |  5330 |     3   (0)| 00:00:01 |
|   1 |  COUNT                          |       |       |       |            |          | -- !
|*  2 |   FILTER                        |       |       |       |            |          |
|   3 |    VIEW                         |       |    82 |  5330 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | T1    |    82 |  5330 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING| T1_X1 |    15 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

cursor_sharing 파라미터를 FORCE 설정하고 ROWNUM 슈도 칼럼에 리터럴을 사용한 쿼리를 수행해보자. ROWNUM = 1이 ROWNUM=:SYS_B_1로 변경되었지만 예상 실행 계획과 달리 COUNT STOPKEY가 동작하는 것을 확인할 수 있다.^[각주:1] 알고 있던 사실과 다른 의외의 결과다.

-- 3
ALTER SESSION SET cursor_sharing = FORCE;

SELECT *
  FROM (SELECT * FROM t1 WHERE c1 >= 1 ORDER BY c1 DESC)
 WHERE ROWNUM = 1;

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY                 |       |      1 |      1 |00:00:00.01 |       3 | -- !
|   2 |   VIEW                         |       |      1 |      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T1    |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN DESCENDING| T1_X1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=:SYS_B_1)
   4 - access("C1">=:SYS_B_0)

1. 11.2 버전에서 테스트했음 [본문으로]