Re: Help: SELECT FROM (subquery) ? Is it possible?

Build index begin from 'start with' field and include all of fields you use in the 'start with' clause.
Use hint to drive the query (but i thing that oracle optimizer will use the index automaticly)
Example:
SELECT RPAD(' ', 7*(LEVEL-1))||w_id, F_YEAR, parent_w_id,p_num FROM wt_table
START WITH w_id = wt_id_par
AND f_year = wt_f_year_par
AND p_num = wt_num_par
CONNECT BY PRIOR w_id = parent_w_id
AND f_year = wt_f_year_par
AND p_num = wt_p_num_par;

For order by you have to build the index: CREATE UNIQUE INDEX WT_PARENT ON WT_table(PARENT_W_ID, F_YEAR, P_NUM, W_ID);

The end;

In article <3886F3BE.5D91C2B7_at_NOSPAMTHANX.hotmail.com>,   "s. hunter" <sarahxhunter_at_NOSPAMTHANX.hotmail.com> wrote:
> Peder,
>
> Have a look at the responses to "Order by clause in subquery" from
Tuesday
> (comp.databases.oracle.server thread started by Shaojie Hu). I think
the
> consensus is "you can't do it unless you have Oracle 8i"..
>
> /Sarah
>
> Peder wrote:
>
> > Hey,
> >
> > I have a problem concerning subqueries and ORDER BY. I have a
query
> > which select a number of records and then does some ordering of
them (using
> > CONNECT BY). However, I want to ORDER the table the SELECT operates
on, like
> > this:
> >
> > SELECT *
> > FROM (SELECT * FROM sometable ORDER BY something)
> > WHERE ...
> > < connect by stuff >
> >
> > instead of
> >
> > SELECT *
> > FROM sometable
> > WHERE ....
> > < connect by stuff >
> >
> > However, this doesn't seem to work. Why? Is there a way to do this.
The
> > problem I have that I can't order after the CONNECT BY, as this
screws up
> > the nice tree-like structure the CONNECT BY made.
> >
> > Thanks in advance,
> > - Peder
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 09:44:38 CST