Re: Query to return PK sequence gap ??

On Oct 12, 2:53 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Oct 10, 5:59 pm, Brian Tkatch <N/A> wrote:
>
>
>
> > On Wed, 10 Oct 2007 15:30:15 +0200, codadilupo
>
> > <codadil..._at_operamail.com> wrote:
> > >Brian Tkatch wrote:
>
> > >> But this solution does not work in 9i.It does work in 10g.
>
> > >It doesn't work in 9.0.1 but it works in 9.2.
>
> > I stand corrected.
>
> > I quicky tested by running: select 1 from dual connect by level <=
> > 10; on its own. That does not work in 9.2, but does work in 10g. As a
> > subquery, however, it works even in 9.2.
>
> > B.
>
> Works for me in 9.2.0.8. Note, however, that there can only be up to
> 100 levels in hierarchical queries, that's why I used two subqueries
> and multiplication to go beyond 100. MINUS looks more neat than my
> original NOT EXISTS syntax-wise, but not sure how they differ in
> performance - NOT EXISTS does a unique scan of PK index in a nested
> loop while MINUS does full scan of the table, two unique sorts and
> then subtracts one unique set from the other...
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

Hmm... It appears that hierarchy is not limited to 100 levels when connect by without filtering is used in a subquery, so maybe that multiplication was unnecessary...

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Oct 12 2007 - 06:03:22 CDT