返事はいつも「それは純真な考えだと思います」です。
とてもシンプルに見えるSQL文でさえ不可能ものです。なぜそうでしょうか。
Oracleが積極的にQuery Transformationを実行するからです。
簡単な例で説明して見ましょう。
このSQL文はどのように実行計画が作られましょうか。
UKJA@ukja102> explain plan for
2 select /*+ */
3 outer.*
4 from ( select * from scott.emp outer
5 union all
6 select * from scott.emp outer) outer
7 where outer.sal > (select /*+ */ avg(inner.sal)
8 from scott.emp inner
9 where inner.deptno = outer.deptno
10 );
Explained.
私のデータベースには次のような実行計画に表現されます。十分に予測可能な実行計画だと思っていませんか。
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 9 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | | 28 | 2436 | 6 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 7 | | |
|* 7 | TABLE ACCESS FULL | EMP | 5 | 35 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM
"SCOTT"."EMP" "INNER" WHERE "INNER"."DEPTNO"=:B1))
7 - filter("INNER"."DEPTNO"=:B1)
でも、subqueryにunnestヒントを付けるものだけで実行計画に大きい変化が発生します。Subquery UnnestingによりSubqueryがInline Viewに変わってしまいます。
UKJA@ukja102> explain plan for
2 select /*+ */
3 outer.*
4 from ( select * from scott.emp outer
5 union all
6 select * from scott.emp outer) outer
7 where outer.sal > (select /*+ unnest */ avg(inner.sal)
8 from scott.emp inner
9 where inner.deptno = outer.deptno
10 );
Explained.
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 113 | 11 (19)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 113 | 11 (19)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | VIEW | | 28 | 2436 | 6 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
PUSH_PREDヒントを付けるともっと極的な変化が現れます。Hash JoinがNested Loops Joinに変わり、Join条件がViewの内に入られます。これがUNION ALL PUSHED PREDICATEで表現されます。
UKJA@ukja102> select /*+ gather_plan_statistics PUSH_PRED(OUTER) */
2 outer.*
3 from ( select * from scott.emp outer
4 union all
5 select * from scott.emp outer) OUTER
6 where outer.sal > (select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
7 from scott.emp inner
8 where inner.deptno = outer.deptno
9 );
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 12 |
| 2 | VIEW | VW_SQ_1 | 1 | 3 | 3 |
| 3 | HASH GROUP BY | | 1 | 3 | 3 |
| 4 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |
| 5 | VIEW | | 3 | 1 | 12 |
| 6 | UNION ALL PUSHED PREDICATE | | 3 | | 12 |
|* 7 | TABLE ACCESS FULL | EMP | 3 | 1 | 6 |
|* 8 | TABLE ACCESS FULL | EMP | 3 | 1 | 6 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(("OUTER"."SAL">"VW_COL_1" AND "OUTER"."DEPTNO"="DEPTNO"))
8 - filter(("OUTER"."SAL">"VW_COL_1" AND "OUTER"."DEPTNO"="DEPTNO"))
CBQT(Cost Based Query Transformation)を非活性化するとヒントがなくても同じ効果を得られます。
UKJA@ukja102> alter session set "_optimizer_cost_based_transformation" = off;
UKJA@ukja102> alter session set "_optimizer_push_pred_cost_based" = false;
UKJA@ukja102> explain plan for
2 select /*+ */
3 outer.*
4 from ( select * from scott.emp outer
5 union all
6 select * from scott.emp outer) outer
7 where outer.sal > (select /*+ */ avg(inner.sal)
8 from scott.emp inner
9 where inner.deptno = outer.deptno
10 );
Explained.
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 9 (23)|
|* 1 | HASH JOIN | | 1 | 65 | 9 (23)|
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)|
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)|
| 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)|
| 5 | VIEW | | 28 | 1092 | 4 (0)|
| 6 | UNION-ALL PARTITION| | | | |
| 7 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)|
| 8 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)|
----------------------------------------------------------------------
Oracle 10gからはQuery Transformationの大部分をOracle自身で決定します。したがって、SQL文を見るだけで実行計画を予測するのはほとんど不可能です。
重要なものはいつも実際の実行計画をきちんと確認し、SQL文がどんな順序で実行されるかを理解するのです。無理な予測は役に立たないだけでなく、むしろ危ないことになってしまうかも知れません。
Posts
