Re: HELP sorting a table before hierarchical query

Try to use index hint inside select. Something like this:

  select /*+ index(your_table your_index) */ lpad(...

And think about to have only one (maybe dummy) root for the best results.

Hope this helps.

BPoulin wrote:
>
> Is there any way I can sort a table (short of completely recreating
> it,unless there is a -good- way of automating that) so that a hierarchical
> query will retrieve the information in alphabetical order (within each
> subgroup of the hierarchy)?
>
> The query will be
>
> select lpad('&nbsp',5*(level-1))||current_concept,parent_concept
> from tables.concept_hierarchy
> start with parent_concept='Concepts'
> connect by prior current_concept=parent_concept;
>
> This worked fine and was easy to read when I created the table using 'create
> ... as select ... order by' but after any insertions into the structure the
> ordering is lost. I've tried to copy all the records to another table,
> sorting them as I go, and then copying them back(again with 'order by') but
> the order is still lost. I've tried this in SQL*PLUS and with a cursor in a
> procedure. I've also tried using a view to sort the records before doing the
> hierarchical query but this is not supported.
>
> The readability of the results of this query is crucial so I have to do this
> somehow. I'm hoping I'm not going to have to recreate the table every time I
> insert something. This will be output to a web page.
>
> Thanks in advance.

--
Valeri Sorokine
ProSoft, Russia, Moscow, Information Systems Division Phone: +7 (095) 234 0636 (6 lines) FAX: +7 (095) 234 0640 E-mail: vsorokin_at_dd.ru OR vsorokin_at_prosoft.ru http://www.dd.ru Received on Wed Aug 25 1999 - 00:34:38 CDT