Re: Connect By / Hierarchy help

Something you can try :

create an index on term1 1:

create index index on term1 1 on thesaurus(term1 1)

change your query in:

select /*+ INDEX(thesaurus index on term1 1) */ lpad(' ',level*2,' ') ||  

term1 1 as term
from thesaurus
start with term1 1 = 'hardwood'
connect by broader id = prior class id

If you want to sort on 'lower(term1 1)' you can maybe create a function-based index.

Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2001-02-05, 21:21:05, schreef "James Alexander Starritt" <james_at_jamesstarritt.com> over het thema Connect By / Hierarchy help:

> I created the following SQL statement to display a heirarchy listing f
rom
a
> database.
 

> select lpad(' ',level*2,' ') || lower(term1 1) as term
> from thesaurus
> start with term1 1 = 'hardwood'
> connect by broader id = prior class id
 

> It works great but items on a similar level are not sorted in
 alphabetical
> order and I would like them to be. There are over 25,000 terms within

this
> listing. If I user order by it either complains or the tree does not

appear
> the way it should, I get output listing the terms in alphabetical orde
r
by
> they lose their parent and children records.
 

> I can't change the structure of the table, so I assuemed I could creat
 e a
> view that would suck all the records out and place them in a specific

order.
> Order By and Create View won't work together..... this can't be
 impossible
> .... what other options do I have here ?
 

> --
 

> James Alexander Starritt (james_at_jamesstarritt.com)
Received on Tue Feb 06 2001 - 14:05:10 CST