뷰 내부에 ROWNUM 슈도 칼럼을 사용하면 FPD(Filter Push-Down)가 동작하지 않는다. 이로 인해 쿼리 성능이 저하될 수 있다. 이번 글에서는 패키지 변수를 사용하여 FPD가 동작하지 않는 뷰의 성능에 대한 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT CEIL (ROWNUM / 500) AS c1, ROWNUM AS c2, 'X' AS c3 FROM XMLTABLE ('1 to 1000');
CREATE INDEX t1_x1 ON t1 (c1, c2);
CREATE INDEX t1_x2 ON t1 (c2);
아래 뷰가 성능 개선 대상이다.
-- 2
CREATE OR REPLACE VIEW v1 AS
SELECT *
FROM (SELECT *
FROM t1
ORDER BY c2 DESC)
WHERE ROWNUM <= 1;
아래는 뷰를 사용한 쿼리다. 3-2번 쿼리는 결과가 반환되지만, 3-1번 쿼리는 결과가 반환되지 않는다. 뷰가 수행된 후 c1 = 1 조건을 필터링하기 때문이다. 성능 측면에서는 테이블을 전체 스캔한 것을 확인할 수 있다.
-- 3-1
SELECT * FROM v1 WHERE c1 = 1;
선택된 레코드가 없습니다.
---------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 | 4 |
|* 1 | VIEW | V1 | 1 | 0 | 4 | -- !
|* 2 | COUNT STOPKEY | | 1 | 1 | 4 |
| 3 | VIEW | | 1 | 1 | 4 |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 1 | 4 |
| 5 | TABLE ACCESS FULL | T1 | 1 | 1000 | 4 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=1)
2 - filter(ROWNUM<=1)
4 - filter(ROWNUM<=1)
-- 3-2
SELECT * FROM v1 WHERE c1 = 2;
C1 C2 C3
-- ---- --
2 1000 X
1개의 행이 선택되었습니다.
---------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 4 |
|* 1 | VIEW | V1 | 1 | 1 | 4 |
|* 2 | COUNT STOPKEY | | 1 | 1 | 4 |
| 3 | VIEW | | 1 | 1 | 4 |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 1 | 4 |
| 5 | TABLE ACCESS FULL | T1 | 1 | 1000 | 4 |
---------------------------------------------------------------------
아래와 같이 패키지를 생성하자. pkg_view_params 패키지에 파라미터를 정의했고, pkg_view_param 패키지로 파라미터를 조회(get), 설정(set)한다.
-- 4
CREATE OR REPLACE PACKAGE pkg_view_params
IS
c1 VARCHAR2(4000);
c2 VARCHAR2(4000);
END;
/
CREATE OR REPLACE PACKAGE pkg_view_param
AS
FUNCTION fnc_get (i_name VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
PROCEDURE prc_set (i_name VARCHAR2, i_value VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_view_param
IS
FUNCTION fnc_get (i_name VARCHAR2) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
RETURN CASE i_name
WHEN 'C1' THEN pkg_view_params.c1
WHEN 'C2' THEN pkg_view_params.c2
ELSE NULL
END;
END;
PROCEDURE prc_set (i_name VARCHAR2, i_value VARCHAR2)
IS
BEGIN
CASE i_name
WHEN 'C1' THEN pkg_view_params.c1 := i_value;
WHEN 'C2' THEN pkg_view_params.c2 := i_value;
ELSE NULL;
END CASE;
END;
END;
/
실행 계획을 최적화하기 위해 아래처럼 다소 복잡한 뷰를 작성했다.
-- 5
CREATE OR REPLACE VIEW v2 AS
SELECT *
FROM (SELECT *
FROM (SELECT /*+ INDEX_DESC(T1 (C2)) */
*
FROM t1
WHERE pkg_view_param.fnc_get ('C1') IS NULL -- !
AND c2 IS NOT NULL
ORDER BY c2 DESC)
WHERE ROWNUM <= 1)
UNION ALL
SELECT *
FROM (SELECT *
FROM (SELECT /*+ INDEX_DESC(T1 (C1 C2)) */
*
FROM t1
WHERE pkg_view_param.fnc_get ('C1') IS NOT NULL -- !
AND c1 = pkg_view_param.fnc_get ('C1') -- !
ORDER BY c2 DESC)
WHERE ROWNUM <= 1);
아래는 pkg_view_param 패키지를 사용한 쿼리다. 6-1번 쿼리도 결과가 반환되었고, 옵션 조건에 따라 최적의 실행계획으로 수행되는 것을 확인할 수 있다.
-- 6-1
EXEC pkg_view_param.prc_set ('C1', '1');
SELECT * FROM v2;
C1 C2 C3
-- --- --
1 500 X
1개의 행이 선택되었습니다.
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 3 |
| 1 | VIEW | V2 | 1 | 1 | 3 |
| 2 | UNION-ALL | | 1 | 1 | 3 |
| 3 | VIEW | | 1 | 0 | 0 |
|* 4 | COUNT STOPKEY | | 1 | 0 | 0 |
| 5 | VIEW | | 1 | 0 | 0 |
|* 6 | SORT ORDER BY STOPKEY | | 1 | 0 | 0 |
|* 7 | FILTER | | 1 | 0 | 0 |
| 8 | TABLE ACCESS FULL | T1 | 0 | 0 | 0 |
| 9 | VIEW | | 1 | 1 | 3 |
|* 10 | COUNT STOPKEY | | 1 | 1 | 3 |
| 11 | VIEW | | 1 | 1 | 3 |
|* 12 | FILTER | | 1 | 1 | 3 |
| 13 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 3 |
|* 14 | INDEX RANGE SCAN DESCENDING| T1_X1 | 1 | 1 | 2 |
--------------------------------------------------------------------------------
-- 6-2
EXEC pkg_view_param.prc_set ('C1', '2');
SELECT * FROM v2;
C1 C2 C3
-- ---- --
2 1000 X
1개의 행이 선택되었습니다.
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 3 |
| 1 | VIEW | V2 | 1 | 1 | 3 |
| 2 | UNION-ALL | | 1 | 1 | 3 |
| 3 | VIEW | | 1 | 0 | 0 |
|* 4 | COUNT STOPKEY | | 1 | 0 | 0 |
| 5 | VIEW | | 1 | 0 | 0 |
|* 6 | SORT ORDER BY STOPKEY | | 1 | 0 | 0 |
|* 7 | FILTER | | 1 | 0 | 0 |
| 8 | TABLE ACCESS FULL | T1 | 0 | 0 | 0 |
| 9 | VIEW | | 1 | 1 | 3 |
|* 10 | COUNT STOPKEY | | 1 | 1 | 3 |
| 11 | VIEW | | 1 | 1 | 3 |
|* 12 | FILTER | | 1 | 1 | 3 |
| 13 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 3 |
|* 14 | INDEX RANGE SCAN DESCENDING| T1_X1 | 1 | 1 | 2 |
--------------------------------------------------------------------------------
-- 6-3
EXEC pkg_view_param.prc_set ('C1', NULL);
SELECT * FROM v2;
C1 C2 C3
-- ---- --
2 1000 X
1개의 행이 선택되었습니다.
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 3 |
| 1 | VIEW | V2 | 1 | 1 | 3 |
| 2 | UNION-ALL | | 1 | 1 | 3 |
| 3 | VIEW | | 1 | 1 | 3 |
|* 4 | COUNT STOPKEY | | 1 | 1 | 3 |
| 5 | VIEW | | 1 | 1 | 3 |
|* 6 | FILTER | | 1 | 1 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 3 |
|* 8 | INDEX FULL SCAN DESCENDING | T1_X2 | 1 | 1 | 2 |
| 9 | VIEW | | 1 | 0 | 0 |
|* 10 | COUNT STOPKEY | | 1 | 0 | 0 |
| 11 | VIEW | | 1 | 0 | 0 |
|* 12 | FILTER | | 1 | 0 | 0 |
| 13 | TABLE ACCESS BY INDEX ROWID | T1 | 0 | 0 | 0 |
|* 14 | INDEX RANGE SCAN DESCENDING| T1_X1 | 0 | 0 | 0 |
--------------------------------------------------------------------------------
'Oracle > Tuning' 카테고리의 다른 글
| DUAL CONNECT BY 쿼리 성능 개선 방안 (0) | 2019.09.11 |
|---|---|
| UPDATE 문 성능 개선 사례 (0) | 2019.09.03 |
| 고유 값에 대한 상관 서브 쿼리 성능 개선 방안 (0) | 2019.08.28 |
| Plan Hash Value (0) | 2019.08.26 |
| MERGE JOIN CARTESIAN (0) | 2019.08.23 |
