코딩하는 야매 기타맨

ROWNUM을 이용한 데이터 기본동작

아래문장은 정상적으로 작동한다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT *
FROM (
    SELECT *
    FROM ROWNUM_TEST
    ORDER BY C1
)
WHERE ROWNUM >=1
AND ROWNUM <=5;

C1   C2   C3   
---- ---- ---- 
   1 C     101
   2 D     102
   3 E     103
   4 F     104
   5 G     105

5 rows selected.

아래문장은 정상적으로 작동하지 않는다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT *
FROM (
    SELECT *
    FROM ROWNUM_TEST
    ORDER BY C1
)
WHERE ROWNUM >= 6
AND ROWNUM <= 10;

C1   C2   C3   
---- ---- ---- 

0 rows selected.

ROWNUM       C1   C2   C3   
------------ ---- ---- ---- 
           1    1 C     101     ==> ROWNUM >= 6 AND ROWNUM <= 10 조건에서 조회되지 않고 버려짐.
           2    2 D     102     ==> 위의  행이 버려졌으므로 다시 ROWNUM은 1이 되고 조건에 의해 버려짐
           3    3 E     103     ==> 이하 동일
           4    4 F     104
           5    5 G     105
           6    6 H     106
           7    7 I     107
           8    8 J     108
           9    9 K     109
          10   10 L     110

위의 조회가 정상적으로 되려면 아래와 같이 조회한다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT C1, C2, C3 
FROM (
    SELECT ROWNUM RN
        , T1.*
    FROM (
        SELECT * FROM ROWNUM_TEST ORDER BY C1
    ) T1
)
WHERE RN >= 6 AND RN <= 10;

C1   C2   C3   
---- ---- ---- 
   6 H     106
   7 I     107
   8 J     108
   9 K     109
  10 L     110

5 rows selected.

NULL 조회시 성능개선

1. INDEX가 존재하는 컬럼을 WHERE 조건에 IS NULL로 조회하는 방법.

1.1 해당 컬럼에 Default Value를 지정해서 column_name IS NULL 조회 보다 column_name = default value 형태로 조회한다.

1.2 FBI(Function Based Index) 생성으로 조회시 Index를 사용할 수 있도록 변경한다.

2. INDEX가 존재하는 컬럼을 WHERE 조건에 IS NOT NULL로 조회하는 방법.

2.1 CHAR/VARCHAR2 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name > CHR(0) 형태로 조회한다.

2.2 DATE 타입의 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name > TO_DATE('19000101','YYYYMMDD') 형태로 조회한다.

(19000101은 해당 컬럼에 존재할 수 없는 이전 값)

2.3 NUMBER 타입의 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name >= 0 OR column_name < 0 형태로 조회한다.

WHERE절에 number, varchar2 형변환 우선순위