Re: Query to return PK sequence gap ??

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 12 Oct 2007 10:53:16 -0000
Message-ID: <1192186396.307518.227960@e9g2000prf.googlegroups.com>

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 Received on Fri Oct 12 2007 - 05:53:16 CDT