꿈꾸는 개발자, DBA 커뮤니티 구루비

1. 부분 범위처리 가능 VS 부분 범위처리 불가능

ex) 부분 범위처리가 가능한 예


- TABLE
EMP = 100만건

- BIND 변수
:B1 = 3
:B2 = 10

SQL> SELECT x1.*
     FROM  (SELECT ROWNUM rum
                  ,e.*
            FROM   (SELECT *
                    FROM   emp
                    ORDER BY deptno) e
            WHERE  ROWNUM <= :B2) x1
     WHERE  x1.rum  >= :B1;

위 SQL은 COUNT STOPKEY 오퍼레이션에 의해 ORDER BY 절에 기술된 DEPTNO순서대로 정렬된 데어터를 10건 추출 후 종료하고
그 데이터를 인라인뷰 밖에서 ROWNUM이 3번부터 10까지 데이터를 가져오는 SQL문이다.

이 처럼 SQL에서 추추될 데이터는 많지만, 일부 데이터만 빠르게 추출하기 위해 조건으로 사용할 수 있는것이 ROWNUM이다.
그래서 페이지 처리 시 부분범위 처리가 가능하도록 SQL을 작성하기 위한 필수 요건 중 하나가 바로 ROWNUM이다.

만일 ROWNUM을 부분범위 처리가 되도록 사용하지 않으면, 전체범위 처리로 수행되어 SQL 성능개선에 도움을 주지 못한다.

ex) 부분 범위처리가 불가능한 예(CASE1)


- TABLE
EMP = 100만건

- BIND 변수
:B1 = 3
:B2 = 10

SQL> SELECT x1.*
     FROM  (SELECT ROWNUM rum
                  ,e.*
            FROM   (SELECT *
                    FROM   emp
                    ORDER BY deptno
                    ) e
            ) x1
     WHERE  x1.rum >= :B1
     AND    x1.rum <= :B2;

ex) 부분 범위처리가 불가능한 예(CASE2)


- TABLE
EMP = 100만건

- BIND 변수
:B1 = 3
:B2 = 10

SQL> SELECT x1.*
     FROM  (SELECT ROWNUM rum
                  ,e.*
            FROM   (SELECT *
                    FROM   emp
                    ORDER BY deptno
                    ) e
            ) x1
     WHERE  x1.rum BETWEEN :B1 AND :B2;

위 2개 SQL같은 경우 모두 전체 데이터를 조회하고, 정렬 후 전체 데이터 중 ROWNUM이 3 ~ 10에 매칭되는 데이터를 추출

2. ROWNUM처리에 따른 SQL성능 차이

2-1. Script 테스트 데이터 생성


SQL> DROP TABLE t1 PURGE;

1. 생성 요건
- 테이블 데이터 건수는 1,000,000 ROWS
- COLUMN c1은 값의 종류가 1,000,000개, UNIQUE 성격의 컬럼

2. 테이블 생성
SQL> CREATE TABLE t1
     AS
     SELECT 1000000-(LEVEL-1) c1
           ,to_char(SYSDATE-(LEVEL-1),'YYYYMMDD') c2
           ,LEVEL c3
     FROM   dual
     CONNECT BY LEVEL <= 1000000;

3. 각 컬럼에 인덱스 생성 및 통계정보 수집
SQL> CREATE INDEX idx_t1_01 ON t1(c2,c1);

BEGIN
    dbms_stats.gather_table_stats(ownname=> 'SCOTT'
                                 ,tabname=> 'T1'
                                 ,estimate_percent=>99
                                 ,method_opt=>'FOR ALL INDEXED COLUMNS'
                                 ,granularity=>'ALL'
                                 ,CASCADE=>TRUE
                                 ,no_invalidate=>FALSE);
END;
/

화면설명

추출 조건
: T1테이블에서 C2(날짜 데이터)컬럼 조건에 의해 오늘 이전의 모든 데이터를 화면에 출력하여야 한다는 요건이 있다.
하지만 오늘 이전의 데이터를 모두 추출하고자 할 경우 데이터가 너무 많아 페이지 처리를 하였다.
이때 각 페이지에 C2, C1컬럼을 내림차순으로 정렬한 데이터를 50건씩 보여줘야 한다.

오늘 이전의 모든 데이터를 추출하고 c2, c1컬럼으로 정렬하는 SQL


SQL> SELECT *
     FROM   t1
     WHERE  c2 <= to_char(SYSDATE, 'YYYYMMDD')
     ORDER BY c2 DESC, c1 DESC;

<교제예제>

1. 부분범위처리가 되지 않는 부적절한 ROWNUM 처리 적용 SQL(C2+C1순으로 결합 인덱스 IDX_T1_01이 존재함)


- BIND 변수
:B1 = 10
:B2 = 30

SQL> SELECT *
     FROM  (SELECT ROWNUM rum
                  ,x.*
            FROM  (SELECT *
                   FROM   t1
                   WHERE  c2 <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                   ORDER BY c2 DESC, c1 DESC) x
            )
     WHERE  rum >= :B1 AND rum <= :B2

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.002          0          0          0          0
Fetch        4    1.840        5.092       3321       3343          0         21
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        6    1.840        5.094       3321       3343          0         21

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     21   FILTER  (cr=3343 pr=3321 pw=0 time=3715942 us)
     21    VIEW  (cr=3343 pr=3321 pw=0 time=3715935 us cost=6920 size=44999955 card=999999)
1000000     COUNT  (cr=3343 pr=3321 pw=0 time=5457477 us)
1000000      VIEW  (cr=3343 pr=3321 pw=0 time=5017693 us cost=6920 size=31999968 card=999999)
1000000       SORT ORDER BY (cr=3343 pr=3321 pw=0 time=4599154 us cost=6920 size=18999981 card=999999)
1000000        TABLE ACCESS FULL T1 (cr=3343 pr=3321 pw=0 time=442169 us cost=967 size=18999981 card=999999)

전체 데이터를 처리한 후 20건을 추출!!

2. 부분범위 처리가 되지 않는 부적절한 ROWNUM 처리 적용 SQL


- BIND 변수
:B1 = 10
:B2 = 30

SQL> SELECT *
     FROM  (SELECT ROWNUM rum
                  ,x.*
            FROM  (SELECT /*+ INDEX(T1 IDX_T1_01)*/
                          *
                   FROM   t1
                   WHERE  c2 <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                   ORDER BY c2 DESC, c1 DESC) x
            )
     WHERE  rum BETWEEN :B1 AND :B2

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.002          0          0          0          0
Fetch        4    4.660       14.162       6805     533899          0         21
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        6    4.660       14.166       6805     533899          0         21

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     21   FILTER  (cr=533899 pr=6805 pw=0 time=12789371 us)
     21    VIEW  (cr=533899 pr=6805 pw=0 time=12789360 us cost=538922 size=44999955 card=999999)
1000000     COUNT  (cr=533899 pr=6805 pw=0 time=14613582 us)
1000000      VIEW  (cr=533899 pr=6805 pw=0 time=14175205 us cost=538922 size=31999968 card=999999)
1000000       SORT ORDER BY (cr=533899 pr=6805 pw=0 time=13760376 us cost=538922 size=18999981 card=999999)
1000000        TABLE ACCESS BY INDEX ROWID T1 (cr=533899 pr=6805 pw=0 time=3309031 us cost=532970 size=18999981 card=999999)
1000000         INDEX RANGE SCAN IDX_T1_01 (cr=3485 pr=3485 pw=0 time=6063613 us cost=3503 size=0 card=998783)(Object ID 127544440)

전체 데이터를 처리한 후 20건을 추출!!

3. 부분범위 처리가 되도록 적절한 ROWNUM 처리를 적용한 SQL(c2+c1순으로 결합 인덱스 IDX_T1_01가 존재함)


- BIND 변수
:B1 = 10
:B2 = 30

SQL> SELECT *
     FROM  (SELECT ROWNUM rum
                  ,x.*
            FROM  (SELECT /*+ INDEX(T1 IDX_T1_01)*/
                          *
                   FROM   t1
                   WHERE  c2 <= TO_CHAR(SYSDATE, 'YYYYMMDD')
                   ORDER BY c2 DESC, c1 DESC) x
            WHERE  ROWNUM   <= :B2
            )
     WHERE  rum >= :B1

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        4    3.900        9.628       6805     533899          0         21
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        6    3.900        9.628       6805     533899          0         21

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     21   VIEW  (cr=533899 pr=6805 pw=0 time=9627422 us cost=538922 size=44999955 card=999999)
     30    COUNT STOPKEY (cr=533899 pr=6805 pw=0 time=9627451 us)
     30     VIEW  (cr=533899 pr=6805 pw=0 time=9627415 us cost=538922 size=31999968 card=999999)
     30      SORT ORDER BY STOPKEY (cr=533899 pr=6805 pw=0 time=9627381 us cost=538922 size=18999981 card=999999)
1000000       TABLE ACCESS BY INDEX ROWID T1 (cr=533899 pr=6805 pw=0 time=3612942 us cost=532970 size=18999981 card=999999)
1000000        INDEX RANGE SCAN IDX_T1_01 (cr=3485 pr=3485 pw=0 time=3234654 us cost=3503 size=0 card=998783)(Object ID 127544440)

<실제예제>

1. 부분 범위처리가 적절하지 못한 예제


SQL> SELECT a.*
     FROM  (SELECT ROWNUM rum
                  ,x.*
            FROM   T1 x
            WHERE  c2 <= TO_CHAR(SYSDATE,'YYYYMMDD')
            ORDER BY c2 DESC, c1 DESC) a
     WHERE  a.rum <= :B1

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.001          0          0          0          0
Fetch       11    1.840        2.472       3321       3343          0        100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       13    1.840        2.474       3321       3343          0        100

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
    100   VIEW  (cr=3343 pr=3321 pw=0 time=1696721 us cost=6920 size=44999955 card=999999)
1000000    SORT ORDER BY (cr=3343 pr=3321 pw=0 time=2577140 us cost=6920 size=18999981 card=999999)
1000000     COUNT  (cr=3343 pr=3321 pw=0 time=809444 us)
1000000      TABLE ACCESS FULL T1 (cr=3343 pr=3321 pw=0 time=379901 us cost=967 size=18999981 card=999999)

2. 부분 범위처리가 적절한 예제


SQL> SELECT a.*
     FROM  (SELECT ROWNUM rum
                  ,x.*
            FROM   T1 x
            WHERE  c2 <= TO_CHAR(SYSDATE,'YYYYMMDD')
            AND    ROWNUM <= :B2
            ORDER BY c2 DESC, c1 DESC) a
     WHERE  a.rum <= :B1

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.002          0          0          0          0
Fetch       11    0.010        0.005         14          3          0        100
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       13    0.010        0.007         14          3          0        100

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
    100   VIEW  (cr=3 pr=14 pw=0 time=4123 us cost=6920 size=44999955 card=999999)
    300    SORT ORDER BY (cr=3 pr=14 pw=0 time=4266 us cost=6920 size=18999981 card=999999)
    300     COUNT STOPKEY (cr=3 pr=14 pw=0 time=3999 us)
    300      TABLE ACCESS FULL T1 (cr=3 pr=14 pw=0 time=3892 us cost=967 size=18999981 card=999999)

정리
: 테스트를 통해 알아본것과 같이 ROWNUM 처리 방법이 SQL성능에 미치는 영향이 매우 크다.
그렇다고 ROWNUM을 무조건 사용하기 보다는 부분범위 처리할때 사용하면 성능부하를 최소화 시킬 수 있다.

ROWNUM 처리를 잘하자. (by hj4867) [2013.12.05]