connect by與where條件執行順序問題

-----------層次查詢中查詢條件在connect by後執行

文件：
《SQL REFERENCE》
SELECT/hierarchical_query_clause
The hierarchical_query_clause lets you select rows in a hierarchical order.
SELECT statements that contain hierarchical queries can contain the LEVEL pseudocolumn in the select list. LEVEL returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, and so on. The number of levels returned by a hierarchical query may be limited by available user memory.
Oracle processes hierarchical queries as follows:
（1）A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
（2）The CONNECT BY condition is evaluated.
（3）Any remaining WHERE clause predicates are evaluated.
If you specify this clause, then do not specify either ORDER BY or GROUP BY, because they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.


舉例：
--無查詢條件
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    JONES    /KING/JONES
3    SCOTT    /KING/JONES/SCOTT
4    ADAMS    /KING/JONES/SCOTT/ADAMS
5    FORD    /KING/JONES/FORD
6    SMITH    /KING/JONES/FORD/SMITH
7    BLAKE    /KING/BLAKE
8    ALLEN    /KING/BLAKE/ALLEN
9    WARD    /KING/BLAKE/WARD
10    MARTIN    /KING/BLAKE/MARTIN
11    TURNER    /KING/BLAKE/TURNER
12    JAMES    /KING/BLAKE/JAMES
13    CLARK    /KING/CLARK
14    MILLER    /KING/CLARK/MILLER

--查詢條件在connect by後執行
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
where ename<>'KING'
start with mgr is null
connect by prior empno=mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    JONES    /KING/JONES
2    SCOTT    /KING/JONES/SCOTT
3    ADAMS    /KING/JONES/SCOTT/ADAMS
4    FORD    /KING/JONES/FORD
5    SMITH    /KING/JONES/FORD/SMITH
6    BLAKE    /KING/BLAKE
7    ALLEN    /KING/BLAKE/ALLEN
8    WARD    /KING/BLAKE/WARD
9    MARTIN    /KING/BLAKE/MARTIN
10    TURNER    /KING/BLAKE/TURNER
11    JAMES    /KING/BLAKE/JAMES
12    CLARK    /KING/CLARK
13    MILLER    /KING/CLARK/MILLER

--connect by中的條件在除過第一行外執行
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr and ename<>'KING';
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    JONES    /KING/JONES
3    SCOTT    /KING/JONES/SCOTT
4    ADAMS    /KING/JONES/SCOTT/ADAMS
5    FORD    /KING/JONES/FORD
6    SMITH    /KING/JONES/FORD/SMITH
7    BLAKE    /KING/BLAKE
8    ALLEN    /KING/BLAKE/ALLEN
9    WARD    /KING/BLAKE/WARD
10    MARTIN    /KING/BLAKE/MARTIN
11    TURNER    /KING/BLAKE/TURNER
12    JAMES    /KING/BLAKE/JAMES
13    CLARK    /KING/CLARK
14    MILLER    /KING/CLARK/MILLER

--connect by中的條件在除過第一行外執行，可以過濾掉一個分支
select rownum,ename,sys_connect_by_path(ename,'/') from scott.emp
start with mgr is null
connect by prior empno=mgr and ename<>'JONES';
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    BLAKE    /KING/BLAKE
3    ALLEN    /KING/BLAKE/ALLEN
4    WARD    /KING/BLAKE/WARD
5    MARTIN    /KING/BLAKE/MARTIN
6    TURNER    /KING/BLAKE/TURNER
7    JAMES    /KING/BLAKE/JAMES
8    CLARK    /KING/CLARK
9    MILLER    /KING/CLARK/MILLER

--查詢條件放入子查詢，效果與將查詢條件放在connect by中（除了第一行外）
select rownum, ename, sys_connect_by_path(ename, '/')
  from (select * from scott.emp where ename <> 'KING')
 start with mgr is null
connect by prior empno = mgr;

no rows selected

--查詢條件放入子查詢，效果與將查詢條件放在connect by中（除了第一行外）
select rownum, ename, sys_connect_by_path(ename, '/')
  from (select * from scott.emp where ename <> 'JONES')
 start with mgr is null
connect by prior empno = mgr;
ROWNUM    ENAME    SYS_CONNECT_BY_PATH(ENAME,'/')
1    KING    /KING
2    BLAKE    /KING/BLAKE
3    ALLEN    /KING/BLAKE/ALLEN
4    WARD    /KING/BLAKE/WARD
5    MARTIN    /KING/BLAKE/MARTIN
6    TURNER    /KING/BLAKE/TURNER
7    JAMES    /KING/BLAKE/JAMES
8    CLARK    /KING/CLARK
9    MILLER    /KING/CLARK/MILLER