« CentOS5.5のVirtualBoxも4.0にアップデートした | トップページ | 階層問合せか、再帰問合せか、それが問題だ #2 »

2011年1月26日 (水)

階層問合せか、再帰問合せか、それが問題だ

Oracle11g R2から再帰問合せができるようになった。階層問合せのように利用できるわけだけど…気になっていたのでちょっと試してみた。

実行計画を見る限り、11g R2 11.2.0.1.0では、階層問合せが対象表を1度だけアクセスするのに対し、WITH句を利用した再帰問合せは対象表を2度アクセスしているし、ソート回数も再帰問合せの方が多いので、対象表大きい場合や、データ量が多い場合、今のところ階層問合せが有利のように見える。
(そのうち、階層問合せと同じ実行計画になるように仕様変更されるとかあるんですかねぇ〜)

#ほかにも違いがありそうだからあとで調べる=>TODO

文法も再帰問合せの方が面倒だけど、細かな操作は書きやすいのだろうかねぇ〜。

・Hierarchical Query - 階層問合せ(オラクルの方言だけど…この方言は好き)

SQL> r
1 with
2 employees as
3 (
4 select
5 empno,
6 ename,
7 job,
8 mgr,
9 level
10 from
11 emp
12 start with
13 mgr is null
14 connect by
15 prior empno = mgr
16 )
17 select *
18 from
19* employees

実行計画
----------------------------------------------------------
Plan hash value: 422387769

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 728 | 4 (25)| 00:00:01 |
| 1 | VIEW | | 14 | 728 | 4 (25)| 00:00:01 |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 308 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("MGR"=PRIOR "EMPNO")
filter("MGR" IS NULL)

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1207 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed


EMPNO ENAME JOB MGR LEVEL
---------- ---------- --------- ---------- ----------
7839 KING PRESIDENT 1
7566 JONES MANAGER 7839 2
7788 SCOTT ANALYST 7566 3
7876 ADAMS CLERK 7788 4
7902 FORD ANALYST 7566 3
7369 SMITH CLERK 7902 4
7698 BLAKE MANAGER 7839 2
7499 ALLEN SALESMAN 7698 3
7521 WARD SALESMAN 7698 3
7654 MARTIN SALESMAN 7698 3
7844 TURNER SALESMAN 7698 3
7900 JAMES CLERK 7698 3
7782 CLARK MANAGER 7839 2
7934 MILLER CLERK 7782 3

14行が選択されました。

経過: 00:00:00.00
SQL>

・Recursive Query - ANSI SQL99対応だけど、今のところ好きじゃない。長いんだもん。

SQL> 
SQL> r
1 with
2 employees (
3 empno,
4 ename,
5 job,
6 mgr,
7 lvl
8 ) as (
9 select
10 empno,
11 ename,
12 job,
13 mgr,
14 1 lvl
15 from
16 emp
17 where
18 mgr is null
19 union all
20 select
21 e1.empno,
22 e1.ename,
23 e1.job,
24 e1.mgr,
25 e2.lvl + 1
26 from
27 emp e1 join employees e2
28 on e2.empno = e1.mgr
29 )
30 search depth first by mgr, empno set order#
31 select
32 empno,
33 ename,
34 job,
35 mgr,
36 lvl
37 from
38 employees
39 order by
40* order#

実行計画
----------------------------------------------------------
Plan hash value: 2042363665

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 156 | 10 (10)| 00:00:01 |
| 1 | VIEW | | 3 | 156 | 10 (10)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 22 | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 2 | 96 | 7 (15)| 00:00:01 |
| 5 | RECURSIVE WITH PUMP | | | | | |
|* 6 | TABLE ACCESS FULL | EMP | 13 | 286 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("MGR" IS NULL)
4 - access("E2"."EMPNO"="E1"."MGR")
6 - filter("E1"."MGR" IS NOT NULL)

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
1205 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
14 rows processed




EMPNO ENAME JOB MGR LVL
---------- ---------- --------- ---------- ----------
7839 KING PRESIDENT 1
7566 JONES MANAGER 7839 2
7788 SCOTT ANALYST 7566 3
7876 ADAMS CLERK 7788 4
7902 FORD ANALYST 7566 3
7369 SMITH CLERK 7902 4
7698 BLAKE MANAGER 7839 2
7499 ALLEN SALESMAN 7698 3
7521 WARD SALESMAN 7698 3
7654 MARTIN SALESMAN 7698 3
7844 TURNER SALESMAN 7698 3
7900 JAMES CLERK 7698 3
7782 CLARK MANAGER 7839 2
7934 MILLER CLERK 7782 3

14行が選択されました。

経過: 00:00:00.00
SQL>

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/50688648

この記事へのトラックバック一覧です: 階層問合せか、再帰問合せか、それが問題だ:

コメント

コメントを書く