• 휴리스틱 기반의 쿼리 변환 : 특정 조건을 충족시키는 경우 적용
• 비용 기반의 쿼리 변환 : 비용 추정기가 산출한 비용에 근거하여, 원래 실행 계획보다 더 낮은 비용의 실행 계획이 생성될 경우 적용

쿼리 변환 - 조건 푸시 다운

필터 푸시 다운(휴리스틱 기반)

----- Current SQL Statement for this session (sql_id=4gptcutk7ntam) -----
EXPLAIN PLAN FOR SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) WHERE id = 1
sql_text_length=86
sql=EXPLAIN PLAN FOR SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) WHERE id = 1
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name        | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                |             |       |       |     4 |           |
| 1   |  VIEW                           |             |     2 |   130 |     4 |  00:00:01 |
| 2   |   SORT UNIQUE                   |             |     2 |   130 |     4 |  00:00:01 |
| 3   |    UNION-ALL                    |             |       |       |       |           |
| 4   |     TABLE ACCESS BY INDEX ROWID | T1          |     1 |    65 |     1 |  00:00:01 |
| 5   |      INDEX UNIQUE SCAN          | SYS_C0017711|     1 |       |     1 |  00:00:01 |
| 6   |     TABLE ACCESS BY INDEX ROWID | T2          |     1 |    65 |     1 |  00:00:01 |
| 7   |      INDEX UNIQUE SCAN          | SYS_C0017712|     1 |       |     1 |  00:00:01 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("T1"."ID"=1)
7 - access("T2"."ID"=1)

조인 푸시 다운(비용 기반)

----- Current SQL Statement for this session (sql_id=4gz8h8zfbgtsu) -----
EXPLAIN PLAN FOR SELECT /*+ push_pred(@"SEL$1" "T23"@"SEL$1" 1) */ * FROM t1, (SELECT * FROM t2 UNION SELECT * FROM t3) t23 WHERE t1.id = t23.id
sql_text_length=145
sql=EXPLAIN PLAN FOR SELECT /*+ push_pred(@"SEL$1" "T23"@"SEL$1" 1) */ * FROM t1, (SELECT * FROM t2 UNION SELECT * FROM t3) t23 WHERE t1.id = t23.id
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id  | Operation                        | Name       | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                 |            |       |       |     4 |           |
| 1   |  NESTED LOOPS                    |            |     1 |   119 |     4 |  00:00:01 |
| 2   |   TABLE ACCESS FULL              | T1         |     1 |    65 |     2 |  00:00:01 |
| 3   |   VIEW                           |            |     1 |    54 |     2 |  00:00:01 |
| 4   |    SORT UNIQUE                   |            |     2 |   130 |     2 |  00:00:01 |
| 5   |     UNION ALL PUSHED PREDICATE   |            |       |       |       |           |
| 6   |      TABLE ACCESS BY INDEX ROWID | T2         |     1 |    65 |     1 |  00:00:01 |
| 7   |       INDEX UNIQUE SCAN          | SYS_C006498|     1 |       |     1 |  00:00:01 |
| 8   |      TABLE ACCESS BY INDEX ROWID | T3         |     1 |    65 |     1 |  00:00:01 |
| 9   |       INDEX UNIQUE SCAN          | SYS_C006499|     1 |       |     1 |  00:00:01 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
7 - access("T2"."ID"="T1"."ID")
9 - access("T3"."ID"="T1"."ID")