지난 글에 이어 이번 글에서는 테이블 표현식을 사용하는 테이블 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")
관련 링크