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

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

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

＃ほかにも違いがありそうだからあとで調べる＝＞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>