ROWNUM을 사용하는 FETCH FIRST 변환

Oracle 23ai(23.4)부터 ROW LIMITING 절을 사용한 쿼리가 분석 함수가 아닌 ROWNUM 슈도 칼럼을 사용하는 쿼리로 변환됩니다.

 

아래는 쿼리 변환과 관련된 Fix Control입니다.

-- 1
SELECT bugno, value, sql_feature, description, optimizer_feature_enable
  FROM v$system_fix_control
 WHERE bugno = 35915968;
 
   BUGNO VALUE SQL_FEATURE                   DESCRIPTION                             OPTIMIZER_FEATURE_ENABLE
-------- ----- ----------------------------- --------------------------------------- ------------------------
35915968     1 QKSFM_TRANSFORMATION_35915968 fetch first transformation using rownum 23.1.0

1 row selected.

 

테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다.

-- 2
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 10000');
CREATE INDEX t1_x1 ON t1 (c1);

 

아래 예제의 10053 트레이스 결과에서 ROW LIMITING 절을 사용한 쿼리가 ROWNUM 슈도 칼럼을 사용하는 쿼리로 변환된 것을 확인할 수 있습니다.

-- 3-1
SELECT   *
    FROM t1
   WHERE c1 > 0
ORDER BY c1 FETCH FIRST 10 ROWS ONLY;

-- 3-2: 10053 trace
Final query after transformations: qb SEL$2 (#0):******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."C1" "C1"
     , "from$_subquery$_002"."C2" "C2"
  FROM (SELECT   "T1"."C1" "C1"
               , "T1"."C2" "C2"
            FROM "TUNA"."T1" "T1"
           WHERE "T1"."C1" > 0
        ORDER BY "T1"."C1") "from$_subquery$_002"
 WHERE ROWNUM <= 10

-------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |         |       |       |     3 |           |
| 1   |  COUNT STOPKEY                 |         |       |       |       |           |
| 2   |   VIEW                         |         |    10 |   260 |     3 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | T1      |    10 |    80 |     3 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | T1_X1   |       |       |     2 |  00:00:01 |
-------------------------------------------------+-----------------------------------+

 

Fix Control을 비활성화하면 이전 버전처럼 ROW LIMITING 절을 사용한 쿼리가  분석 함수를 사용하는 쿼리로 변환됩니다.

-- 4-1
ALTER SESSION SET "_fix_control" = '35915968:0';

SELECT   *
    FROM t1
   WHERE c1 > 0
ORDER BY c1 FETCH FIRST 10 ROWS ONLY;

-- 4-2: 10053 trace
Final query after transformations: qb SEL$2 (#1):******* UNPARSED QUERY IS *******
SELECT   "from$_subquery$_002"."C1" "C1"
       , "from$_subquery$_002"."C2" "C2"
    FROM (SELECT "T1"."C1" "C1"
               , "T1"."C2" "C2"
               , "T1"."C1" "rowlimit_$_0"
               , ROW_NUMBER () OVER (ORDER BY "T1"."C1") "rowlimit_$$_rownumber"
            FROM "TUNA"."T1" "T1"
           WHERE "T1"."C1" > 0) "from$_subquery$_002"
   WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <= 10
ORDER BY "from$_subquery$_002"."rowlimit_$_0"

-------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |         |       |       |     3 |           |
| 1   |  VIEW                          |         |    10 |   520 |     3 |  00:00:01 |
| 2   |   WINDOW NOSORT STOPKEY        |         |    10 |    80 |     3 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | T1      |    10 |    80 |     3 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | T1_X1   |       |       |     2 |  00:00:01 |
-------------------------------------------------+-----------------------------------+