Re: Efficiency of hierarchical queries

On Mon, 02 Feb 1998 13:06:53 -0500, Keith D Gregory <keith_at_inconcert.com> 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?
>

Hi Keith,

I assume that you have a table like this:

some_id PRIMARY KEY
parent_id NULL FOREIGN KEY references your_table(some_id) ...

I suggest that you do use referential integrity constraints. The only thing that you must take care of then is to have an index on that parent_id column. For CONNECT BY queries, Oracle may then use only index scans on your PK and FK indexes to build the tree of your result set. Another option would be to have a concatenated index on (parent_id, some_id), in that order. Just try it out and put your queries through explain plan. BTW, what optimizer mode do use ? If it's CBO make sure your tables and indexes are analyzed.

HTH
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net