树状sql中connect by里prior的含义！

搜集了几篇和树状sql相关的几篇文章...

http://www.bithink.cn/bbs/viewthread.php?tid=87&extra=page%3D2&sid=lyPZPp

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.bithink.cn/bbs/viewthread.php?tid=87&extra=page%3D2&sid=lyPZPp

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1043517/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1043517/