RE: Hint for self-join connect by

Ciao Alberto

> 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.

Just an observation...

I don't think it is the case because if you have an additional column you will see something like:

| Operation                     | Name                       |
--------------------------------------------------------------
|  CONNECT BY WITH FILTERING    |                            |
|   TABLE ACCESS BY INDEX ROWID | DCH_WORK_SURR_MGMT_ORDERED |
|    INDEX RANGE SCAN           | TEST_INDEX                 |
|   NESTED LOOPS                |                            |
|    BUFFER SORT                |                            |
|     CONNECT BY PUMP           |                            |
|    TABLE ACCESS BY INDEX ROWID| DCH_WORK_SURR_MGMT_ORDERED |
|     INDEX RANGE SCAN          | TEST_INDEX2                |
|   TABLE ACCESS FULL           | DCH_WORK_SURR_MGMT_ORDERED |
--------------------------------------------------------------

In other words the index-only scan is replaced by a normal table access via rowid.

Buona giornata,
Chris

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