SQL Macro - Table Expression

지난 글에 이어 이번 글에서는 테이블 표현식을 사용하는 테이블 SQM에 대해 간단히 살펴보겠습니다. 참고로 테이블 SQM은 Oracle 19c에 추가된 PTF(Polymorphic Table Function)과 유사하게 동작하며, SQL Server의 TVF(Table-Valued Function)을 대체할 수 있는 기능으로 보입니다.

 

테스트 버전은 아래와 같습니다. 테이블 SQM은 19.7 버전으로 기능이 backport되었습니다.

-- 1
SELECT version_full FROM product_component_version;
 
VERSION_FULL
------------
21.3.0.0.0
 
1 row selected.

 

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

-- 2
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
 
CREATE TABLE t1 (c1, c2) AS SELECT CEIL (ROWNUM / 10), NVL (NULLIF (MOD (ROWNUM, 10), 0), 10)     FROM XMLTABLE ('1 to 100');
CREATE TABLE t2 (c1, c2) AS SELECT CEIL (ROWNUM / 10), NVL (NULLIF (MOD (ROWNUM, 10), 0), 10) + 1 FROM XMLTABLE ('1 to 100');

 

아래와 같이 함수의 RETURN 절 끝에 SQL_MACRO를 기술하면 테이블 SQM을 생성할 수 있습니다. 쿼리에 f1 함수를 사용하면 테이블 표현식을 실행한 결과가 반환됩니다.

-- 3-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
    RETURN 'SELECT c2 FROM t1 WHERE c1 = i_c1';
END;
/
 
-- 3-2
SELECT * FROM f1 (1);
 
C2
--
 1
...
10 rows selected.

 

10053 트레이스를 살펴보면 변환된 쿼리를 확인할 수 있습니다.

-- 4-1
SELECT sql_id, sql_text FROM v$sql WHERE sql_text = 'SELECT * FROM f1 (1)';
 
------------- --------------------
5rwmp7z9zdrnc SELECT * FROM f1 (1)
 
1 row selected.
 
-- 4-2
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C2" "C2" FROM "TUNA"."T1" "T1" WHERE "T1"."C1"=1

 

테이블 SQM은 DBMS_TF 패키지의 DBMS_TF.TABLE_T 타입을 지원합니다.

-- 5-1
CREATE OR REPLACE FUNCTION f1 (
    i_tab IN DBMS_TF.TABLE_T
)
    RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
    RETURN 'SELECT c2 FROM i_tab WHERE c1 = 1';
END;
/
 
-- 5-2
SELECT * FROM f1 (t2);
 
C2
--
 2
...
10 rows selected.

 

아래는 테이블 SQM에 DBMS_TF.COLUMNS_T 타입을 사용한 예제입니다. 

-- 6-1
CREATE OR REPLACE FUNCTION f1 (
    i_col IN DBMS_TF.COLUMNS_T
)
    RETURN VARCHAR2 SQL_MACRO
IS
    v_col VARCHAR2(32767);
BEGIN
    FOR i IN 1 .. i_col.COUNT LOOP
        v_col := v_col || i_col(i) || CASE WHEN i < i_col.COUNT THEN ',' END;
    END LOOP;
 
 -- SELECT LISTAGG (column_value, ',') INTO v_col FROM TABLE (i_col);
    RETURN 'SELECT ' || v_col || ' FROM t1 WHERE c1 = 1';
END;
/
 
-- 6-2
SELECT * FROM f1 (COLUMNS (c1, c2));
 
C1 C2
-- --
 1  1
...
10 rows selected.

 

테이블 SQM을 사용하면 FPD(Filter PushDown)가 동작하지 않는 뷰의 성능을 개선할 수 있습니다. 다소 극단적 예제이지만 아래 뷰는 FPD가 동작하지 않습니다.

-- 7-1
CREATE INDEX t2_x1 On t2 (c1);
 
CREATE OR REPLACE VIEW v1 AS
SELECT   *
    FROM t2
ORDER BY c1, c2 FETCH FIRST 2 ROWS ONLY;
 
-- 7-2
SELECT * FROM v1 WHERE c1 = 2;
 
no rows selected
 
-----------------------------------------------------------
| Id  | Operation                | Name | Starts | A-Rows |
-----------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |      0 |
|*  1 |  VIEW                    |      |      1 |      0 |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |      2 |
|   3 |    TABLE ACCESS FULL     | T2   |      1 |    100 |
-----------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("from$_subquery$_003"."C1"=2 AND "from$_subquery$_003"."rowlimit_$$_rownumber"<=2))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "T2"."C1","T2"."C2")<=2)

 

아래와 같이 테이블 SQM을 사용하면 조건이 정상적으로 동작하는 것을 확인할 수 있습니다.

-- 8-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
    RETURN 'SELECT * FROM t2 WHERE c1 = i_c1 ORDER BY c1, c2 FETCH FIRST 2 ROWS ONLY';
END;
/
 
-- 8-2
SELECT * FROM f1 (2);
 
C1 C2
-- --
 2  2
 2  3
 
2 rows selected.
 
-------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | A-Rows |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |      2 |
|*  1 |  VIEW                                 |       |      1 |      2 |
|*  2 |   WINDOW SORT PUSHED RANK             |       |      1 |      2 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |     10 |
|*  4 |     INDEX RANGE SCAN                  | T2_X1 |      1 |     10 |
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "T2"."C1","T2"."C2")<=2)
   4 - access("C1"=2)

 

아래 예제는 t1 테이블과 f1 함수를 조인했지만 JPPD가 동작하지 않은 것을 확인할 수 있습니다.

-- 9-1
CREATE OR REPLACE FUNCTION f1 (
    i_c1 IN NUMBER
)
    RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
    RETURN 'SELECT /*+ NO_MERGE */ * FROM t2 WHERE c1 = i_c1';
END;
/
 
-- 9-2
SELECT /*+ LEADING(A) USE_NL(B) */ * FROM t1 a, f1 (a.c1) b WHERE a.c2 = 1;
 
------------------------------------------------------
| Id  | Operation           | Name | Starts | A-Rows |
------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |   1000 |
|   1 |  NESTED LOOPS       |      |      1 |   1000 |
|*  2 |   TABLE ACCESS FULL | T1   |      1 |     10 |
|   3 |   VIEW              |      |     10 |   1000 |
|   4 |    TABLE ACCESS FULL| T2   |     10 |   1000 |
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."C2"=1)

 

10053 트레이스를 살펴보면 쿼리 변환에 오류가 있는 것을 확인할 수 있습니다. 테이블 SQM에 대한 조인이 Lateral View를 사용하도록 기능이 개선되어야 할 것 같습니다.

-- 10-1
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ LEADING ("A") USE_NL ("SYS__$") */
       "A"."C1" "C1"
     , "A"."C2" "C2"
     , "SYS__$"."C1" "C1"
     , "SYS__$"."C2" "C2"
  FROM "TUNA"."T1" "A"
     , (SELECT /*+ NO_MERGE */
               "T2"."C1" "C1"
             , "T2"."C2" "C2"
          FROM "TUNA"."T2" "T2"
         WHERE "T2"."C1" = "A"."C1") "SYS__$"
 WHERE "A"."C2" = 1
 
-- 10-2
SELECT /*+ LEADING(A) USE_NL(B) */
       *
  FROM t1 a
     , LATERAL (SELECT /*+ NO_MERGE */ * FROM t2 WHERE c1 = a.c1) b
 WHERE a.c2 = 1;
 
-----------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Starts | A-Rows |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |      1 |    100 |
|   1 |  NESTED LOOPS                         |                 |      1 |    100 |
|*  2 |   TABLE ACCESS FULL                   | T1              |      1 |     10 |
|   3 |   VIEW PUSHED PREDICATE               | VW_DCL_A18161FF |     10 |    100 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     10 |    100 |
|*  5 |     INDEX RANGE SCAN                  | T2_X1           |     10 |    100 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."C2"=1)
   5 - access("T2"."C1"="A"."C1")

 

관련 링크

• ORACLE-BASE - SQL Macros in Oracle Database 21c
• SQLORA - Tag Archives: SQL macros
• An Oracle Programmer - Category Archives: SQL table macros