Re: Efficiency of hierarchical queries

Keith D Gregory wrote:
>
> Looking at the execution plan for a hierarchical query, I see a table
> scan, no matter what I do for indexing hints. It seems to me that this
> will result in very poor performance for the table in question, which
> could contain several million rows.
>
> So, is there something magical about the CONNECT BY operation, or will
> it really scan my table?
>
> -kdg

1. Check your START WITH clause A common problem arises if you want to start at the top of the tree and use something like "start with parent_id is null". The optimiser MUST then do a full table scan to determine the start point(s). So make sure you have an indexable query for this clause.
2. In the CONNECT by clause, where you are using something like "connect by prior col1 = col2" the only usable index is one which starts with col2, since the value of col2 for each row is derived from the value for col1 in the previous row.
3. Make sure that you do not have two or more OR'd conditions in the CONNECT BY clause, since this will prevent the optimiser doing anything useful.
4. Remember that conditions in the WHERE clause (if any) do NOT use an index, since this clause only prunes leaves from the branches found from the STAR WITH ... CONNECT BY ... clauses.

If these guidelines are followed, you should have no trouble.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"