Re: HELP sorting a table before hierarchical query

From: Valeri Sorokine <>
Date: Wed, 25 Aug 1999 09:34:38 +0400
Message-ID: <>

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: OR
Received on Wed Aug 25 1999 - 07:34:38 CEST

Original text of this message