Re: Hint for self-join connect by

From: amit poddar <amit.poddar_at_yale.edu>
Date: Tue, 01 May 2007 15:08:10 -0400
Message-ID: <4637901A.3090608@yale.edu>

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:

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 01 2007 - 14:08:10 CDT