ROWNUM 이해하기

 

ROWNUM 이란?

ROWNUM 은 Oracle 에서 제공하는 가상 컬럼이다.

Select 절에서 사용할 경우, 추출하는 데이터의 순번을 부여하는 용도로 사용된다. 

Where 절에서 사용할 경우, 추출할 데이터 중 일부만 가져올 용도로 사용된다. 특히, where 절에 사용되는 ROWNUM은 인덱스의 특성을 이용하여 부분범위 처리로 유도할 수 있어 잘 사용하면 효율적인 SQL이 된다.

 

ROWNUM 사용시 주의 사항

 ROWNUM 데이터를 먼저 추출한 이후 조회하자

ROWNUM 은 SQL 결과 셋에 순번을 1부터 부여하는 가상컬럼으로써, Fetch 단계에서 추출하는 첫 번째 로우에 1을 부여한다. 그리고 다음 로우 추출시 ROWNUM을 +1 한다. 따라서 1이 존재하지 않으면 다음 순번을 부여할 수가 없다.

즉, ROWNUM을 이용하여 단 한 건(ROWNUM=1)의 데이터만 추출하거나, 1부터 시작하여 순서대로 N개의 데이터를 추출하는 것은 가능하지만, ROWNUM의 시작을 1이 아닌 값부터 추출하려 할 경우에는 데이터가 추출되지 않는다.

 

ORDER BY 와 ROWNUM을 같은 위치에 두지 말자

ORDER BY 절이 ROWNUM 조건과 동일한 위치에 있다면, ROWNUM 조건을 먼저 처리한 후 ORDER BY 절이 수행된다. 따라서 ORDER BY 절로 데이터를 정렬한 후 ROWNUM으로 몇 개의 데이터만 추출하려면, ORDER BY 절을 먼저 처리할 수 있도록 인라인 뷰를 만들고 그 뷰를 감싸는 바깥 SQL 에서 ROWNUM 처리를 해야한다.

 

참고) SQL 구성 요소들이 수행되는 순서

1) FROM, WHERE 절을 처리

2) ROWNUM 조건 적용

3) SELECT COLUMN LIST 절 적용

4) GROUP BY 절 적용

5) HAVING 절 적용

6) ORDER BY 절 적용

 

ROWNUM=1 은 ROWNUM<=1 로 사용하자

ROWNUM=1 과 ROWNUM<=1 조건에서 1인 상수로 처리됐다면 2개의 조건의 결과와 성능은 동일하다.

단, DB 파라미터 CURSOR_SHARING 설정 값을 FORCE 또는 SIMILAR 로 변경하면, 1은 Bind 변수로 변경된다.

이로 인해 부분범위 처리로 수행하던 SQL 이 전체범위 처리로 수행되어 심각한 성ㄴ으 문제가 발생할 가능성이 있다. 그러므로 SQL 에 ROWNUM 조건을 추가할 때 가급적 ROWNUM=1 보다는 ROWNUM <=1로 작성하는 습관을 가지는 것이 좋겠다.

 

** CURSOR_SHARING 파라미터를 FORCE 또는 SIMILAR 로 적용했을 때

조건 1) ROWNUM = 1 ---> ROWNUM =:"SYS_B_0"

CURSOR_SHARING = FORCE 또는 SIMILAR를 적용하면 ROWNUM = 1 은 ROWNUM=:"SYS_B_0"와 같이 상수 처리 부분을 강제로 Bind 변수로 변경한다. 이로 인해 Optimizer 가 상수 처리를 바인드 변수에 어떤 값이 입력될지 알 수 없어 전체 데이터를 검색한 이후 1건을 추출하는 방식으로 수행한다. 상수 1이 그대로 사용되었다면 부분범위처리로 빠르게 1건을 추출한 후 수행을 종료했을 것이다. 이때 sql 실행계획의 OPERATION 은 COUNT로 보이게 된다. (STOPKEY가 제공되지 않는다)

 

조건 2) ROWNUM <= 1 ---> ROWNUM <= :"SYS_B_0"

등호 조건에서는 Bind 변수 값이 어떤 값인지 알 수는 없지만, "SYS_B_0"값보다 작거나 같은 값이라는 범위(STOPKEY)가 정해진다. 따라서 Optimizer 는 "SYS_B_0" 변수의 값을 확인하여 작거나 같은 데이터를 추출한다. 그러므로 전체 데이터를 처리하지 않고 1건만 추출하고 SQL의 수행은 종료된다. 이 때 SQL 실행계획의 OPERATION은 COUNT STOPKEY 이다. 

COUNT STOPKEY 오퍼레이션은 :"SYS_B_0"을 만나면, STOPKEY가 발동하고 SQL 의 수행을 중단하여 부분범위 처리를 할 수 있게 된다.

 

- INDEX_DESC와 ROWNUM<=1을 함께 사용하지 말자

특정 컬럼의 최대값 또는 최소값을 추출하는 방법 중에 INDEX_DESC(또는 INDEX_ASC) 힌트와 ROWNUM<=1 조건을 함께 사용하는 방법이 있다. 이는 인덱스의 데이터가 정렬되어 있다는 특성을 이용하는 것으로, ORDR BY 절을 제거해도 정렬 작업을 하지 않기 때문에 성능상 매우 유리한 방법이다. 그러나 만약 인덱스 상태가 Unusable 상태이거나 인덱스가 존재하지 않는 경우에는 무작위로 1건의 데이터를 추출하기 때문에 잘못된 데이터가 추출될 수 있다는 치명적인 단점이 있어 사용 시 각별한 주의가 필요하다. 따라서 데이터 정합성이 훼손되지 않으면서 효율적인 성능을 가지는 아래의 2가지 형태로 작성하자

SQL 1) ORDER BY 절을 먼저 처리한 후, 이를 인라인 뷰로 만들고 ROWNUM 조건을 처리하여 성능 문제 제거

SELECT C1
  FROM (
  		SELECT /*+ INDEX_DESC(T1 IDX_01) */
        		C1
          FROM ROWNUM_T1 T1
         WHERE T1.C2 = 'A'
           AND T1.C1 > 0
         ORDER BY C2 DESC, C1 DESC
        ) 
 WHERE ROWNUM <= 1;

SQL2) 인덱스의 정렬된 데이터를 이용한 MIN/MAX 오퍼레이션으로 수행되도록 유도한 케이스, 가장 많이 사용되고 있음

SELECT MAX(C1) AS C1
  FROM ROWNUM_T1 T1
 WHERE T1.C2 = 'A'
   AND T1.C1 >= 0;

2개의 SQL 트레이스 결과를 보면 성능에 큰 차이가 없다.

<ROWNUM_T1>

ㅁ 생성 요건

- 테이블 데이터 건수는 50000로우

- 컬럼 C1의 값 종류는 100,000 가지이며, Unique한 성격을 가진 컬럼

- 컬럼 C2의 값의 종류는 26가지

 

ㅁ 테이블 생성

CREATE TABLE ROWNUM_T1 AS

SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2

FROM DUAL

CONNECT BY LEVEL<=50000;

 

ㅁ 각 컬럼에 인덱스 생성 및 통계정보 수집

CREATE INDEX IDX_01 ON ROWNUM_T1(C2,C1);

 

ROWNUM<=1은 항상 빠르지 않다

ROWNUM<=1 조건은 보통 SQL에서 추출된 데이터 중 WHERE 절 조건을 만족하는 1건만 신속하게 추출하고자 할 때 사용한다. 하지만 이 조건은 SQL 수행 속도를 항상 빠르게 하진 않는다.

WHERE 절에 사용된 조건 컬럼에 인덱스가 있고, 추출되는 데이터가 많아 1건을 신속하게 추출해야 할 때에는 ROWNUM<=1 조건은 SQL 수행 속도를 빠르게 한다. 하지만 WHERE 절에 또다른 조건 컬럼이 인덱스에 포함되지 않고 NUM_DISTINCT 값이 높아 최종 추출 건수가 적다면, ROWNUM<=1 조건은 절대 빠른 수행을 할 수가 없다. 왜냐하면 조건에 맞는 데이터를 찾기 위해 모든 데이터에 대해 확인을 해봐야 하기 때문이다. 따라서 WHERE 절에 ROWNUM<=1 조건이 있지만 빠른 조회가 되지 않는다.

 

SQL1) 빠른 조회가 되는 경우(WHERE 조건이 없거나 인덱스 구성 컬럼만 존재할 때)

-- 1) 조건이 없는 경우
SELECT *
  FROM ROWNUM_T2
 WHERE ROWNUM<=1;
 
 --2) 인덱스 구성 컬럼만 존재하는 경우
 SELECT *
   FROM ROWNUM_T2
  WHERE C2 IN ('P','S','H')
    AND ROWNUM<=1;

SQL2) 느린 조회가 되는 경우(ROWNUM_T2_IDX_01 구성 컬럼은 C2)

SELECT /*+ INDEX(ROWNUM_T2 ROWNUM_T2_IDX_01) */ *
  FROM ROWNUM_T2
 WHERE C2 IN ('P','S','H')
   AND C1 = 1004
   AND ROWNUM <=1;

트레이스 결과를 보면 인덱스에서 C2컬럼 조건으로 2,95,728건을 추출하고 있다. 그리고 C1 컬럼 조건을 처리하기 위해 테이블을 액세스한 후 데이터를 걸러낸다. 하지만 안타깝게도 위 SQL 의 최종 추출 결과는 0건이다. 즉, 데이터를 모두 읽을 때까지 ROWNUM <= 1 을 만족할 수 없어 전체 데이터를 처리하였다. 

이 SQL의 성능 개선을 위해서는 기존 C2 컬럼으로만 구성된 인덱스를 C2+C1 컬럼으로 구성된 결합 인덱스로 변경해야 한다. 이럴 경우 테이블에 액세스하지 않고 인덱스에만 접근한 후 ROWNUM<=1 처리가 가능하기 때문에 성능이 향상된다.

 

따라서 SQL에 ROWNUM<=1 조건이 있는 것만으로 수행 속도가 빠를 것이란 판단이 틀릴 수 있다는 것을 꼭 기억하자

 

인라인 뷰에 ROWNUM 을 추가할 때 주의하자

인라인 뷰에 ROWNUM을 사용하면 Optimizer 는 실행 계획 수립할 때, 먼저 인라인 뷰의 모든 데이터에 ROWNUM 순번을 부여한 후 조인 연결을 해야하기 때무네 뷰 외부 조건이 뷰 내부로 침투되는 Query Transformation 의 종류 중 하나인 View predicating 기능이 사용되지 않는다. 이 경우 성능 문제가 발생할 수 있다. 

 

이 글은 [SQL튜닝의 시작] 서적 내용을 참고하여 작성되었습니다.