SQL> select t.*, prior id, prior parent_id, prior t.name, prior t.code
from test t
start with t.id = 12
connect by prior t.id = t.parent_id
and t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 3043676987
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 84 | 9 (34)| 00:00:01 | | |
|* 1 | CONNECT BY WITH FILTERING | | | | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TEST | 1 | 15 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX UNIQUE SCAN | SYS_C0010758 | 1 | | 0 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 1 | 28 | 6 (17)| 00:00:01 | | |
| 5 | CONNECT BY PUMP | | | | | | | |
| 6 | PARTITION LIST SINGLE | | 2 | 30 | 4 (0)| 00:00:01 | KEY | KEY |
| 7 | TABLE ACCESS FULL | TEST | 2 | 30 | 4 (0)| 00:00:01 | 6 | 6 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."CODE"='0306')
3 - access("T"."ID"=12)
4 - access("connect$_by$_pump$_002"."prior t.id "="T"."PARENT_ID")