UNION ALL 연산자의 Top-N 동작

UNION ALL 연산자의 Top-N 동작에 대한 내용을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, LPAD ('X', 4000, 'X') AS c2 FROM XMLTABLE ('1 to 10');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);

아래 쿼리는 UNION ALL 연산 결과에 Top-N을 사용했다. 자주는 아니지만 수평분할된 테이블이나 유사한 성격의 테이블에 대한 Top-N 처리가 필요할 때 종종 사용되는 패턴이다. t2 테이블은 블록 I/O가 발생하지 않았으므로 Top-N이 동작하는 것을 확인할 수 있다.

-- 2
SELECT c1, LTRIM (c2, 'X') AS c2
  FROM (SELECT * FROM (SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 >= 1 ORDER BY c1)
        UNION ALL
        SELECT * FROM (SELECT /*+ INDEX(T2) */ * FROM t2 WHERE c1 >= 1 ORDER BY c1))
 WHERE ROWNUM <= 10;

-----------------------------------------------------------------------------
| Id  | Operation                       | Name  | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |      1 |     10 |      12 |
|*  1 |  COUNT STOPKEY                  |       |      1 |     10 |      12 |
|   2 |   VIEW                          |       |      1 |     10 |      12 |
|   3 |    UNION-ALL                    |       |      1 |     10 |      12 |
|   4 |     VIEW                        |       |      1 |     10 |      12 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T1    |      1 |     10 |      12 |
|*  6 |       INDEX RANGE SCAN          | T1_X1 |      1 |     10 |       2 |
|   7 |     VIEW                        |       |      0 |      0 |       0 | -- !
|   8 |      TABLE ACCESS BY INDEX ROWID| T2    |      0 |      0 |       0 |
|*  9 |       INDEX RANGE SCAN          | T2_X1 |      0 |      0 |       0 |
-----------------------------------------------------------------------------

N을 15로 늘리면 t1 테이블에서 5건만 조회되는 것을 확인할 수 있다.

-- 3
SELECT c1, LTRIM (c2, 'X') AS c2
  FROM (SELECT * FROM (SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 >= 1 ORDER BY c1)
        UNION ALL
        SELECT * FROM (SELECT /*+ INDEX(T2) */ * FROM t2 WHERE c1 >= 1 ORDER BY c1))
 WHERE ROWNUM <= 15;

-----------------------------------------------------------------------------
| Id  | Operation                       | Name  | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |      1 |     15 |      18 |
|*  1 |  COUNT STOPKEY                  |       |      1 |     15 |      18 |
|   2 |   VIEW                          |       |      1 |     15 |      18 |
|   3 |    UNION-ALL                    |       |      1 |     15 |      18 |
|   4 |     VIEW                        |       |      1 |     10 |      12 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T1    |      1 |     10 |      12 |
|*  6 |       INDEX RANGE SCAN          | T1_X1 |      1 |     10 |       2 |
|   7 |     VIEW                        |       |      1 |      5 |       6 | -- !
|   8 |      TABLE ACCESS BY INDEX ROWID| T2    |      1 |      5 |       6 |
|*  9 |       INDEX RANGE SCAN          | T2_X1 |      1 |      5 |       1 |
-----------------------------------------------------------------------------

아래와 같은 방식으로도 Top-N을 사용할 수 있다. t1에서 5행을 조회하고, t2에서 나머지 행을 조회했다.

-- 4
SELECT c1, LTRIM (c2, 'X') AS c2
  FROM (SELECT * FROM (SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c1 >= 1 ORDER BY c1) WHERE ROWNUM <= 5
        UNION ALL
        SELECT * FROM (SELECT /*+ INDEX(T2) */ * FROM t2 WHERE c1 >= 1 ORDER BY c1))
 WHERE ROWNUM <= 10;

------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |      1 |     10 |      13 |
|*  1 |  COUNT STOPKEY                   |       |      1 |     10 |      13 |
|   2 |   VIEW                           |       |      1 |     10 |      13 |
|   3 |    UNION-ALL                     |       |      1 |     10 |      13 |
|*  4 |     COUNT STOPKEY                |       |      1 |      5 |       7 |
|   5 |      VIEW                        |       |      1 |      5 |       7 | -- !
|   6 |       TABLE ACCESS BY INDEX ROWID| T1    |      1 |      5 |       7 |
|*  7 |        INDEX RANGE SCAN          | T1_X1 |      1 |      5 |       2 |
|   8 |     VIEW                         |       |      1 |      5 |       6 |
|   9 |      TABLE ACCESS BY INDEX ROWID | T2    |      1 |      5 |       6 |
|* 10 |       INDEX RANGE SCAN           | T2_X1 |      1 |      5 |       1 |
------------------------------------------------------------------------------

12.1 버전부터 동작하는 PQ_CONCURRENT_UNION은 UNION ALL 연산자의 SET을 각각 병렬로 수행하기 때문에 UNION ALL 레벨의 Top-N이 동작하지 않는다.

-- 5
SELECT /*+ PQ_CONCURRENT_UNION */
       C1, LTRIM (C2, 'X') AS C2
  FROM (SELECT * FROM (SELECT /*+ INDEX(T1) */ * FROM T1 WHERE C1 >= 1 ORDER BY C1) WHERE ROWNUM <= 5
        UNION ALL
        SELECT * FROM (SELECT /*+ INDEX(T2) */ * FROM T2 WHERE C1 >= 1 ORDER BY C1))
 WHERE ROWNUM <= 10;

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts |    TQ  |IN-OUT| PQ Distrib | A-Rows | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |      |            |     10 |       0 |
|*  1 |  COUNT STOPKEY                       |          |      1 |        |      |            |     10 |       0 |
|   2 |   PX COORDINATOR                     |          |      1 |        |      |            |     10 |       0 |
|   3 |    PX SEND QC (RANDOM)               | :TQ10000 |      0 |  Q1,00 | P->S | QC (RAND)  |      0 |       0 |
|*  4 |     COUNT STOPKEY                    |          |      2 |  Q1,00 | PCWC |            |     15 |      17 | -- !
|   5 |      VIEW                            |          |      2 |  Q1,00 | PCWP |            |     15 |      17 |
|   6 |       UNION-ALL                      |          |      2 |  Q1,00 | PCWP |            |     15 |      17 |
|   7 |        PX SELECTOR                   |          |      2 |  Q1,00 | PCWP |            |      5 |       6 |
|*  8 |         COUNT STOPKEY                |          |      1 |  Q1,00 | PCWP |            |      5 |       6 | -- !
|   9 |          VIEW                        |          |      1 |  Q1,00 | PCWP |            |      5 |       6 |
|  10 |           TABLE ACCESS BY INDEX ROWID| T1       |      1 |  Q1,00 | PCWP |            |      5 |       6 |
|* 11 |            INDEX RANGE SCAN          | T1_X1    |      1 |  Q1,00 | PCWP |            |      5 |       1 |
|  12 |        PX SELECTOR                   |          |      2 |  Q1,00 | PCWP |            |     10 |      11 |
|  13 |         VIEW                         |          |      1 |  Q1,00 | PCWP |            |     10 |      11 |
|  14 |          TABLE ACCESS BY INDEX ROWID | T2       |      1 |  Q1,00 | PCWP |            |     10 |      11 |
|* 15 |           INDEX RANGE SCAN           | T2_X1    |      1 |  Q1,00 | PCWP |            |     10 |       1 |
------------------------------------------------------------------------------------------------------------------