고유 값 구하기

DISTINCT 연산자를 사용하면 쉽게 고유 값을 구할 수 있지만, 테이블 크기가 큰 경우 블록 I/O로 인한 성능 저하가 발생할 수 있다. 성능 개선을 위해 RSF(Resursive Subquery Factoring)로 고유 값을 구해보자.

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

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT CEIL (ROWNUM / 10000) AS c1, LPAD ('X', 20, 'X') AS c2, LPAD ('X', 100, 'X') AS c3
  FROM XMLTABLE ('1 to 1000000');

CREATE INDEX t1_x1 ON t1 (c1);

아래 쿼리는 테이블을 FULL SCAN하여 18,198개의 블록 I/O가 발생했다.

-- 2
SELECT DISTINCT c1 FROM t1;

---------------------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |    100 |   18198 |
|   1 |  HASH UNIQUE       |      |      1 |    100 |   18198 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   18198 |
---------------------------------------------------------------

아래 쿼리는 조건을 추가했다. INDEX FAST FULL SCAN으로 동작하여 1,966개의 블록 I/O가 발생했다.

-- 3
SELECT DISTINCT c1 FROM t1 WHERE c1 > 0;

-------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |      1 |    100 |    1966 |
|   1 |  HASH UNIQUE          |       |      1 |    100 |    1966 |
|*  2 |   INDEX FAST FULL SCAN| T1_X1 |      1 |   1000K|    1966 |
-------------------------------------------------------------------

아래 쿼리는 RSF를 사용했다. 211개의 블록 I/O가 발생한 것을 확인할 수 있다. 고유 값이 인덱스 선두 칼럼이고, 테이블 크기에 비해 고유 값의 개수가 적은 경우 사용해 볼만한 기법이다. 

-- 4
WITH w1 (c1) AS (
SELECT MIN (c1) AS c1
  FROM t1
UNION ALL
SELECT (SELECT MIN (x.c1) FROM t1 x WHERE x.c1 > a.c1) AS c1
  FROM w1 a
 WHERE a.c1 IS NOT NULL)
SELECT *
  FROM w1
 WHERE c1 IS NOT NULL;

---------------------------------------------------------------------------------------
| Id  | Operation                                 | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |       |      1 |    100 |     211 |
|*  1 |  VIEW                                     |       |      1 |    100 |     211 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|       |      1 |    101 |     211 |
|   3 |    SORT AGGREGATE                         |       |      1 |      1 |       3 |
|   4 |     INDEX FULL SCAN (MIN/MAX)             | T1_X1 |      1 |      1 |       3 |
|   5 |    SORT AGGREGATE                         |       |    100 |    100 |     208 |
|   6 |     FIRST ROW                             |       |    100 |     99 |     208 |
|*  7 |      INDEX RANGE SCAN (MIN/MAX)           | T1_X1 |    100 |     99 |     208 |
|   8 |    RECURSIVE WITH PUMP                    |       |    101 |    100 |       0 |
---------------------------------------------------------------------------------------