drop table test;
create table test
(
? id? number primary key,
? parent_id number,
? name varchar2(20),
? code varchar2(4)
)
partition by list(code)
(
? partition p1 values('0301'),
? partition p2 values('0302'),
? partition p3 values('0303'),
? partition p4 values('0304'),
? partition p5 values('0305'),
? partition p6 values('0306'),
? partition p7 values('0307'),
? partition p8 values('0308'),
? partition p_default values (default)
);
insert into test values(1,0,'a1','0301');
insert into test values(2,1,'a2','0301');
insert into test values(3,2,'a3','0301');
insert into test values(4,3,'a4','0301');
insert into test values(5,0,'a5','0302');
insert into test values(6,5,'a6','0302');
insert into test values(7,6,'a7','0302');
insert into test values(8,7,'a8','0302');
insert into test values(9,8,'a9','0302');
insert into test values(10,0,'a10','0303');
insert into test values(11,0,'a11','0304');
insert into test values(12,0,'a12','0306');
insert into test values(13,0,'a13','0307');
insert into test values(14,0,'a14','0308');
insert into test values(15,10,'a15','0303');
insert into test values(16,11,'a16','0304');
insert into test values(17,12,'a17','0306');
insert into test values(18,13,'a18','0307');
insert into test values(19,14,'a19','0308');
commit;
exec dbms_stats.gather_table_stats(user,'test',cascade => true);
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE? ? 11.2.0.1.0? ? ? Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> set autotrace traceonly
SQL> select * from test t
? ? start with t.id = 12
? ? connect by prior t.id = t.parent_id;
执行计划
----------------------------------------------------------
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")
? ? ? filter("T"."ID"=12)
统计信息
----------------------------------------------------------
? ? ? ? ? 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;
执行计划
-------------------------