递归查询(一)：connect by
语法：
{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]| START WITH condition CONNECT BY [ NOCYCLE ] condition
}
START WITH:specifies the root row(s) of the hierarchy.
CONNECT BY:specifies the relationship between parent rows and child rows of the hierarchy.
1.In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row.
2.If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator, although you can have multiple PRIOR conditions.
PRIOR:
1.PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
2.PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations.
note：Both the CONNECT BY condition and the PRIOR expression can take the form of an uncorrelated subquery. However, CURRVAL and NEXTVAL are not valid PRIOR expressions, so the PRIOR expression cannot refer to a sequence.
NOCYCLE:
The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data
ORDER SIBLINGS BY:
Applies an order to siblings, without altering the basic hierarchical structure of the data returned by the query.
拓展：
LEVEL :The position in the hierarchy of the current row in relation to the root node.
CONNECT_BY_ISCYCLE:
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.
CONNECT_BY_ISLEAF :
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.
CONNECT_BY_ROOT :
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries. Restriction on CONNECT_BY_ROOT: You cannot specify this operator in the START WITH condition or the CONNECT BY condition.
SYS_CONNECT_BY_PATH :
SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.
Both column and char can be any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 data type and is in the same character set as column.
例子：
这里引用公司大牛罗海雄老师的sql，我稍稍做了一点修改：
with ash as
(select sample_id,instance_number,session_id,blocking_session,blocking_inst_id,
nvl(event, session_state) event
from dba_hist_active_sess_history
where snap_id = &snap_id)
select * from (
select sample_id,instance_number,session_id,blocking_session,blocking_inst_id,
connect_by_root(session_id),level l,connect_by_isleaf leaf
sys_connect_by_path(event || '(' || instance_number || '-' || session_id || ')', ','),
from ash start with blocking_session is not null
connect by prior blocking_session = session_id
and prior sample_id = sample_id
and prior blocking_inst_id = instance_number)
where leaf = 1
order by 1,2;
该sql的主要目的是查询ash中wait chain，通过connect by找到同一个sample_id下session之间的阻塞关系，并通过connect_by_isleaf可以确定该wait chain的阻塞源，再通过外层的where leaf = 1只列出wait chain阻塞源的session，sys_connect_by_path列出每一条wait chain的具体session以及event。
with ash as
(select sample_id,instance_number,session_id,blocking_session,blocking_inst_id,
nvl(event, session_state) event
from dba_hist_active_sess_history
where snap_id = &snap_id)
select * from (
select sample_id,instance_number,session_id,blocking_session,blocking_inst_id,
connect_by_root(session_id),level l,connect_by_isleaf leaf
sys_connect_by_path(event || '(' || instance_number || '-' || session_id || ')', ','),
from ash start with blocking_session is not null
connect by prior blocking_session = session_id
and prior sample_id = sample_id
and prior blocking_inst_id = instance_number)
where leaf = 1
order by 1,2;
该sql的主要目的是查询ash中wait chain，通过connect by找到同一个sample_id下session之间的阻塞关系，并通过connect_by_isleaf可以确定该wait chain的阻塞源，再通过外层的where leaf = 1只列出wait chain阻塞源的session，sys_connect_by_path列出每一条wait chain的具体session以及event。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31347199/viewspace-2127948/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
1次connect by的优化
下一篇：
没有了~
请登录后发表评论
登录
全部评论
