Re: Hierarchical Query too slow

Sanjay Mishra wrote:

> Ralf Hofmann <hofmannb_at_informatik.uni-muenchen.de> wrote in message news:<3B54513F.B2043159_at_informatik.uni-muenchen.de>...
> > Hallo!
> >
> > My Data is based on a xml-file. The probleme is:
> > find all elements lying under a given element;
> > I do this with the CONNECT BY statement, but this is very slowly if many
> > (about 2.000) elements are under the given element. In this case, the
> > query is much faster, if I check every element(about 40.000) of the
> > tree, if it is under the given element. But this tuning is not nice
> > because I do not know, how many element the query will find. Does anyone
> > know a way to tune the CONNECT BY statement.
> >
> > Thank you for your help.
>
> Try creating an index on the self referential column. For example, if
> you have an employee table with emp_id and mgr_id columns and you
> perform connect by based on mgr_id = prior emp_id, try creating an
> index on mgr_id. This should help you query performance.

Thank you. This works fine.

There was already an index on this column, but in this way: create index index on employee(another_col,mgr_id) This index of course did not help, but I had in mind, that there is an index on this column and so I didn´t check this.

--
Ralf Hofmann

hofmannb_at_informatik.uni-muenchen.de