1. 선택적 조건절(조건이 들어올 수도 있고 생략될 수도 있음)이 있을 경우 Dynamic SQL을 사용하지 않고 해결할 수 있는 방법
2. _or_expand_nvl_predicate = False (True 설정 시 OR-Expansion Using Function 비 활성화)
3. _optimizer_cost_based_transformation 과 관계 없이 동작
| NVL 예제 | |
|---|---|
| {code:sql | borderStyle=solid} var v_loc number; exec :v_loc := 1500; |
SELECT /*+ QB_NAME(MAIN) */
e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND d.location_id = NVL (:v_loc, d.location_id); --1500 대입
Plan hash value: 1611409792
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | 12 | ||||
| 1 | CONCATENATION | |||||
| 2 | FILTER | |||||
| 3 | MERGE JOIN | 64 | 2688 | 6 | 00:00:01 | |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 16 | 320 | 2 | 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 1 | 00:00:01 | |
| 6 | SORT JOIN | 107 | 2354 | 4 | 00:00:01 | |
| 7 | TABLE ACCESS FULL | EMPLOYEE | 107 | 2354 | 3 | 00:00:01 |
| 8 | FILTER | |||||
| 9 | HASH JOIN | 15 | 630 | 6 | 00:00:01 | |
| 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 4 | 80 | 2 | 00:00:01 |
| 11 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | 1 | 00:00:01 | |
| 12 | TABLE ACCESS FULL | EMPLOYEE | 107 | 2354 | 3 | 00:00:01 |
Predicate Information:
-- 쿼리가 아래와 같이 UNION-ALL 분리됨 (:v_loc 가 NULL 인 경우와 그렇지 않은 경우)
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND d.location_id=TO_NUMBER(TO_CHAR(d.location_id)) <<<<<
AND :v_loc IS NULL
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND d.location_id = :v_loc
AND :v_loc IS NOT NULL ;
|
||10053 Trace||
|{code:sql}
{code:4 - filter("D"."LOCATION_ID"=TO_NUMBER(TO_CHAR("D"."LOCATION_ID")))}
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save) [0 1 ]
Transformed predicate "D"."LOCATION_ID"=TO_NUMBER(NVL(:B1,TO_CHAR("D"."LOCATION_ID"))) to
:B1 IS NOT NULL AND "D"."LOCATION_ID"=TO_NUMBER(:B2) OR :B3 IS NULL AND "D"."LOCATION_ID"=TO_NUMBER(TO_CHAR("D"."LOCATION_ID")) <<<<< 불필요한 조건 생성
Trying or-Expansion on query block MAIN (#0)
******** Next OR predicate ********
Trying or-Expansion on query block MAIN (#0)
******** OR-branching ********
or-expansion-subheap (create addr=0x0D74C7C8)
-- NVL 함수를 OR 조건으로 분리하기 전에 D.LOCATION_ID 컬럼에 TO_CHAR 함수가 적용됨 : FPD 과정에서 TO_CHAR 적용 되었으나 쓸모없음
-- 조건절의 컬럼이 NULL 허용일 경우 원하는 결과가 달라질 수 있다, NULL = NULL 연산 으로 인해 데이터 누락 발생 가능 하므로 아래와 같이 수정 가능
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND :v_loc IS NULL
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id
AND d.location_id = :v_loc
AND :v_loc IS NOT NULL ;
|
| DECODE 예제 | |
|---|---|
| {code:sql | borderStyle=solid} SELECT /*+ QB_NAME(MAIN) */ e.employee_id, e.first_name, e.last_name, d.department_name FROM employee e, department d WHERE e.department_id = d.department_id AND d.location_id = DECODE (:v_loc, NULL, d.location_id, :v_loc); |
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | 1 | 45 | 00:00:00.01 | 12 | ||||
| 1 | CONCATENATION | 1 | 45 | 00:00:00.01 | 12 | ||||
| FILTER | 1 | 0 | 00:00:00.01 | 0 | ||||
| HASH JOIN | 0 | 0 | 00:00:00.01 | 0 | 915K | 915K | ||
| 4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 0 | 0 | 00:00:00.01 | 0 | |||
| INDEX FULL SCAN | DEPT_LOCATION_IX | 0 | 0 | 00:00:00.01 | 0 | |||
| 6 | TABLE ACCESS FULL | EMPLOYEE | 0 | 0 | 00:00:00.01 | 0 | |||
| FILTER | 1 | 45 | 00:00:00.01 | 12 | ||||
| 8 | NESTED LOOPS | 1 | 45 | 00:00:00.01 | 12 | ||||
| 9 | NESTED LOOPS | 1 | 45 | 00:00:00.01 | 7 | ||||
| 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 1 | 00:00:00.01 | 3 | |||
| INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 00:00:00.01 | 2 | |||
| INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | 00:00:00.01 | 4 | |||
| 13 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 45 | 45 | 00:00:00.01 | 5 |
Predicate Information (identified by operation id):
2 - filter(TO_CHAR(:V_LOC) IS NULL)
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - filter("D"."LOCATION_ID" IS NOT NULL)
7 - filter(TO_CHAR(:V_LOC) IS NOT NULL)
11 - access("D"."LOCATION_ID"=:V_LOC)
12 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
– NVL 예제와 다른 점은 :v_loc 가 NULL 일 때, "5 - filter("D"."LOCATION_ID" IS NOT NULL)" 조건이 추가 됨에 따라 D.LOCATION_ID 값이 NULL 인 데이터가 존재 한다면 결과가 달라지게 됨
|
||RANK 예제||
|{code:sql|borderStyle=solid}
SQL> var v_top_rank number;
SQL> exec :v_top_rank := 1;
SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */
*
FROM (SELECT cust_id, amount_sold,
RANK () OVER (ORDER BY amount_sold DESC) AS amt
FROM sales)
WHERE amt <= :v_top_rank; --> 변수에 1 대입
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 |00:00:00.01 | | | |
|* 1 | VIEW | | 1 | 108 |00:00:00.01 | | | |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 108 |00:00:00.01 | 73728 | 73728 | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | 109 |00:00:00.01 | | | |
| 4 | INDEX FULL SCAN DESCENDING | IDX_SALES_01 | 1 | 109 |00:00:00.01 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AMT"<=:V_TOP_RANK)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_TOP_RANK)
-- 결과는 108건 이지만 Id 4 에서 109건 읽음, FPD 기능이 분석 함수를 사용할 때도 수행 되고 있으나, 의도한 Plan이 아니며 아래와 같이 기대 했었음
SELECT *
FROM (SELECT /*+ INDEX(sales IDX_SALES_01) */ cust_id, amount_sold,
RANK() OVER (ORDER BY amount_sold DESC) AS amt
FROM sales
WHERE :v_top_rank < :SOME_VALUE --> 오라클이 변수에 적당한 값을 대입하게 된다.
UNION ALL
SELECT /*+ FULL(sales) */ cust_id, amount_sold,
RANK() OVER (ORDER BY amount_sold DESC) AS amt
FROM sales
WHERE :v_top_rank < :SOME_VALUE) A
WHERE amt <= :v_top_rank;
-- 오라클의 문서에 따르면 위의 변환이 가능 하다고 하나 데이터 건수를 늘리거나, 히스토그램 생성, 파라미터 변경 테스트를 했으나 위와 같이 동작하지 않음
-- Query Optimization with Switch Predicates (Mohamed Zauddin 외 1명) : Patent No : US 6581055 B1 (특허, 2003년)
|