Amit, I'm researching as well, the algorithm is not 100% clear to me, and I have yet to RTFM in full and look around for "prior art" :) I have created as well a minitable this afternoon, and I have an interesting variant to share (9.2.0.6): insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('_MERGED_',2,1); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',3,2); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',4,3); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',5,4); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('_MERGED_',6,99); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',7,6); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',8,7); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',9,8); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',10,9); insert into dch_work_surr_mgmt_ordered(ACCT_ID,FORWARD_SURROGATE_ID,SURROGATE_ID) values ('',11,10); using your DDL: create index test_index on dch_work_surr_mgmt_ordered (acct_id); create index test_index2 on dch_work_surr_mgmt_ordered (surrogate_id, forward_surrogate_id); exec dbms_stats.gather_table_stats (user, 'dch_work_surr_mgmt_ordered', cascade=>true, method_opt=>'for all columns size 1', estimate_percent=>100); explain plan for SELECT SUBSTR(sys_connect_by_path(surrogate_id, '|'), 2) tree , LEVEL AS lev FROM dch_work_surr_mgmt_ordered START WITH acct_id = '_MERGED_' CONNECT BY PRIOR forward_surrogate_id = surrogate_id; select * from table (dbms_xplan.display); --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 2 | |* 1 | CONNECT BY WITH FILTERING | | | | | | 2 | NESTED LOOPS | | | | | |* 3 | INDEX RANGE SCAN | TEST_INDEX | 2 | 6 | 1 | | 4 | TABLE ACCESS BY USER ROWID | DCH_WORK_SURR_MGMT_ORDERED | | | | | 5 | NESTED LOOPS | | | | | | 6 | BUFFER SORT | | 1 | 8 | | | 7 | CONNECT BY PUMP | | | | | | 8 | TABLE ACCESS BY INDEX ROWID| DCH_WORK_SURR_MGMT_ORDERED | 1 | 8 | 2 | |* 9 | INDEX RANGE SCAN | TEST_INDEX2 | 1 | | 1 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DCH_WORK_SURR_MGMT_ORDERED"."ACCT_ID"='_MERGED_') 3 - access("DCH_WORK_SURR_MGMT_ORDERED"."ACCT_ID"='_MERGED_') 9 - access("DCH_WORK_SURR_MGMT_ORDERED"."SURROGATE_ID"=NULL) This shows the algorithm as I expect it to be: (a) 3+4 gets the starting rows, feed them to the row sources 5-9. (b) 7 gets the rows, 6 orders them (probably to minimize the number of blocks to read), sends to 8+9 that retrieves the first level of the hierarchy (c) rows retrieved are sent back to (b) until no rows found. I cannot understand row source 8 (FTS) in your plan, why it should FTS ... unless (wild shot in the dark) it keeps in "memory" the rowids only while doing (b)+(c) and then FTS to retrieve the remaining columns. Alberto On 5/1/07, amit poddar <amit.poddar@xxxxxxxx> wrote:
Alberto from your reply it seems that you understand the connect by runtime algorithm quite well. Can you please clarify my confusion ? I have create the table dch_work_surr_mgmt_ordered and created two indexes test_index (acct_id) and test_index2(surrogate_id, forward_surrogate_id) My question is: 1. Step 2 and 3 in the explain plan are for getting the rows for the start with clause (first selection) which step is the hierarchy visit you mention (probably step 7) ? amit SQL> explain plan for 2 SELECT SUBSTR(sys_connect_by_path(surrogate_id, '|'), 2) tree , LEVEL AS lev FROM dch_work_surr_mgmt_ordered START WITH acct_id = '&ACCT_MERGE' CONNECT BY PRIOR forward_surrogate_id = surrogate_id 3 4 5 6 7 / Enter value for acct_merge: aa old 5: START WITH acct_id = '&ACCT_MERGE' new 5: START WITH acct_id = 'aa' Explained. SQL> @?/rdbms/admin/utlxpls.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3651564746 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10M| 247M| 1 (0)| 00:00:01 | |* 1 | CONNECT BY WITH FILTERING | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| DCH_WORK_SURR_MGMT_ORDERED | 10M| 495M| 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | TEST_INDEX | 4000K| | 1 (0)| 00:00:01 | | 4 | NESTED LOOPS | | | | | | | 5 | BUFFER SORT | | | | | | | 6 | CONNECT BY PUMP | | | | | | |* 7 | INDEX RANGE SCAN | TEST_INDEX2 | 10M| 247M| 1 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | DCH_WORK_SURR_MGMT_ORDERED | 1000M| 36G| 93987 (100)| 00:05:49 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("SURROGATE_ID"=PRIOR "FORWARD_SURROGATE_ID") 3 - access("ACCT_ID"=TO_NUMBER('aa')) 7 - access("SURROGATE_ID"=PRIOR "FORWARD_SURROGATE_ID") 22 rows selected. Alberto Dell'Era wrote: > That doesn't seem to tally with any of your plans - neither > has both id 5 and 6 with an asterisk. May you please > check it out and repost both plans with the predicate infos ? > > It would also interesting to know num_distinct, num_null and density > (from dba_tab_columns) for the columns > acct_id, forward_surrogate_id and surrogate_id. > > 450k out of 70M - that's 0.6%. Maybe an index on > acct_id, forward_surrogate_id > may help the first selection, and another on > surrogate_id, forward_surrogate_id > *might* help the hierarchy visit (or at least turn the FTS > into an index FFS). Why not giving it a shot.
-- Alberto Dell'Era "dulce bellum inexpertis" -- //www.freelists.org/webpage/oracle-l