Thursday, December 16, 2010

グローバルヒントの制約の面白いケース

次はグローバルヒントで結合順序を制御できないテストケースです。


1. 表T1, T2, T3を作ります。


SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create table t2(c1 number, c2 number);

Table created.

SQL> create table t3(c1 number, c2 number);

Table created.

2. グローバルヒントを使って、結合順序をT1->T2->T3にしますが、なぜか予想通りに動作しません。

SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ leading(v.t1 v.t2 t3) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 52 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

テストの結果から、Optimizerはグローバルヒントがグローバルではないヒントとは正常的に動作しないことがわります。インラインビューでヒントを与える方法もありますが、この例ではグローバルヒントだけで制御するのが目的です。


3. ここで適用できるのがOracleの内部的なグローバルヒントの表記法です。DBMS_XPLAN.DISPLAY関数にADVANCEDオプションを使えばOracleの内部的なヒント表記法が出力されます。


select * from table(dbms_xplan.display(null, null, 'advanced'));
...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$5C160134
3 - SEL$5C160134 / T1@SEL$3
4 - SEL$5C160134 / T2@SEL$3
5 - SEL$5C160134 / T3@SEL$2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
FULL(@"SEL$5C160134" "T3"@"SEL$2")
FULL(@"SEL$5C160134" "T2"@"SEL$3")
FULL(@"SEL$5C160134" "T1"@"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
MERGE(@"SEL$3")
OUTLINE(@"SEL$335DD26A")
OUTLINE(@"SEL$1")
MERGE(@"SEL$335DD26A")
OUTLINE_LEAF(@"SEL$5C160134")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

4. その結果を応用してグローバルヒントを与えれば、結合順序を完全に制御できます。

SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

5. もっと良い方法はQB_NAMEヒントでしょう。このヒントを使用すればもっと読みやすくて変更しやしはずです。

SQL> explain plan for
2 select * from
3 (
4 select
5 /*+ leading(t1@inline t2@inline t3) */
6 v.c1 as v_c1,
7 v.c2 as v_c2,
8 t3.c2 as t3_c2
9 from
10 (select /*+ qb_name(inline) */
11 t1.c1,
12 t2.c2
13 from
14 t1, t2
15 where
16 t1.c1 = t2.c1) v,
17 t3
18 where
19 v.c1 = t3.c1
20 ) x
21 ;

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

グローバルヒントの使い方を理解するに役に立つ面白いテストケースですね。

No comments:

Post a Comment