Re: Limit the levels for a query with CONNECT BY

le_pul_at_yahoo.ca (Bernard Drolet) wrote in message news:<ee7856eb.0311050650.4cbb0e2a_at_posting.google.com>...
> sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0311050031.2f4d988c_at_posting.google.com>...
> >
> > Hint: there is a LEVEL pseudo function.
> >
> > Sybrand Bakker
> > Senior Oracle DBA
>
> I have two concerns with LEVEL:
>
> First,
>
> I do not know in advance at what level the specific row is ! The table
> contains the structure and content of complex SGML documents; the SGML
> structure (DTD) can be different from one document to the other and
> can even change over time
>
> (for example, I can have a document with
> PUBLICATION - CHAPTER - SECTION - TASK
> structure today, but in future, the person in charge of the
> publication can decide to add a layer, as in
> PUBLICATION - CHAPTER - SECTION - PAGEBLOCK - TASK,
> so the level to find the TASK is now changed and the query will not
> find it anymore).
>
> Second,
>
> Even if I ask to display only the, for example, 3 first LEVELs, am I
> right when I say the query will process the complete tree structure
> anyway before returning me only the rows I'm interested in ?
>
>

Let's try to reverse the task. If name column is indexed, you can use something like this:

select id, name
from Mytable a
where name = 'XYZ'
  and exists (select 1

              from Mytable b
              where id = &1
              connect by id = prior parent_id
              start with name = 'XYZ' and b.id = a.id)

Subquery should represent reverse tree starting from current line of the main query.

>
> Bernard Drolet
Received on Thu Nov 06 2003 - 16:49:22 CET