Oracle分区表的层次查询如何才能用到分区?(二)
--------------------------------
| 0 | SELECT STATEMENT | | 19 | 798 | 16 (7)| 00:00:01 | | |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | | |
| 2 | PARTITION LIST ALL | | 19 | 285 | 15 (0)| 00:00:01 | 1 | 9 |
| 3 | TABLE ACCESS FULL | TEST | 19 | 285 | 15 (0)| 00:00:01 | 1 | 9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."ID"=12 AND "T"."CODE"='0306')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from test t
start with (t.id = 12
and t.code = '0306')
connect by prior t.id = t.parent_id
and prior t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 798 | 16 (7)| 00:00:01 | | |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | | |
| 2 | PARTITION LIST ALL | | 19 | 285 | 15 (0)| 00:00:01 | 1 | 9 |
| 3 | TABLE ACCESS FULL | TEST | 19 | 285 | 15 (0)| 00:00:01 | 1 | 9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."PARENT_ID"=PRIOR "T"."ID" AND PRIOR "T"."CODE"='0306')
filter("T"."ID"=12 AND "T"."CODE"='0306')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
select * from test t
start with t.id = 12
connect by prior t.id = t.parent_id
and prior t.code = '0306';
执行计划
----------------------------------------------------------
Plan hash value: 6144290
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 798 | 16 (7)| 00:00:01 | | |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | | |
| 2 | PARTITION LIST ALL | | 19 | 285 | 15 (0)| 00:00:01 | 1 | 9 |
| 3 | TABLE ACCESS FULL | TEST | 19 | 285 | 15 (0)| 00:00:01 | 1 | 9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------