樹狀sql中connect by裡prior的含義！

蒐集了幾篇和樹狀sql相關的幾篇文章...

http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html

在樹狀sql裡其實最難理解的我覺得就是connect by之後的prior關鍵字了，就像外聯接欄位邊上的+，其實prior和+的意思非常相似，如果我們希望從start with 後面欄位提供的值作為節點檢索它的孩子，那麼就把prior寫在connect by 中關聯條件start with 後面欄位等式的另外一側；如果我們希望從start with 後面欄位提供的值作為節點檢索它的父親，那麼就把prior寫在connect by 中關聯條件start with 後面欄位的一側。比較難表述，看一下下面的例子吧。

[@more@]

http://www.cnblogs.com/ZHF/archive/2008/09/10/1288101.html

--==========================

SQL> set linesize 135
SQL> set autotrace traceonly exp
SQL> select count(*) from dba_objects;

Execution Plan
----------------------------------------------------------
Plan hash value: 2598313856

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 36 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | VIEW | DBA_OBJECTS | 10052 | | 36 (6)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN | | 11687 | 878K| 35 (6)| 00:00:01 |
| 6 | TABLE ACCESS FULL | USER$ | 36 | 108 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | OBJ$ | 11687 | 844K| 32 (4)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 16 | 1 (0)| 00:00:01 |
| 11 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS CLUSTER | USER$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

4 - filter("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
5 - access("O"."OWNER#"="U"."USER#")
7 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_'
AND "O"."LINKNAME" IS NULL)
8 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
9 - access("I"."OBJ#"=:B1)
13 - access("L"."OWNER#"="U"."USER#")

---==================================

為了演示方便首先透過上面的執行計劃顯示了一下計劃的plan_hash_value:2598313856,下面以這個執行計劃中的id和parent_id來演示一下connect by中prior的含義:

SQL> set autotrace off
SQL> select id,parent_id from v$sql_plan where plan_hash_value=2598313856;

ID PARENT_ID
---------- ----------
0
1 0
2 1
3 2
4 3
5 4
6 5
7 5
8 4
9 8
10 3

ID PARENT_ID
---------- ----------
11 10
12 10
13 12

14 rows selected.

--==============================

查詢以parent_id=3為root節點的所有孩子，那麼prior寫在connect by中連線條件的id一側。

SQL> select parent_id,id from (
2 select id,parent_id,sql_id from v$sql_plan where plan_hash_value=2598313856
3 )
4 start with parent_id in (3)
5 connect by prior id = parent_id
6 ;

PARENT_ID ID
---------- ----------
3 4
4 5
5 6
5 7
4 8
8 9
3 10
10 11
10 12
12 13

10 rows selected.

--=================================

查詢以parent_id=3為節點的所有他的父親，，那麼prior寫在connect by中連線條件的partent_id一側。當然從查詢結果來看很顯然也包含了3的孩子4和10，但是更重要的是主要顯示出了3的父親2以及2的父親1還有1的父親0...

SQL> select parent_id,id from (
2 select id,parent_id,sql_id from v$sql_plan where plan_hash_value=2598313856
3 )
4 start with parent_id in (3)
5 connect by id = prior parent_id
6 ;

PARENT_ID ID
---------- ----------
3 4
2 3
1 2
0 1
0
3 10
2 3
1 2
0 1
0

10 rows selected.

--================================

如果把prior寫在connect by中連線條件等號的兩側，那麼說明該節點既是它的孩子也是它的父親，那隻能是它自己了。

SQL> select parent_id,id from (
2 select id,parent_id,sql_id from v$sql_plan where plan_hash_value=2598313856
3 )
4 start with parent_id in (3)
5 connect by prior id = prior parent_id
6 ;

PARENT_ID ID
---------- ----------
3 4
3 10

SQL>