FreeLists / oracle-l / Re: Hint for self-join connect by

[amit poddar <amit.poddar@xxxxxxxx>]

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.
--
http://www.freelists.org/webpage/oracle-l