Re: Select will never come back,

From: Mark D Powell <mark.powell_at_eds.com>
Date: 7 Jun 2002 06:46:08 -0700
Message-ID: <178d2795.0206070546.6fd166c@posting.google.com>

jbruewer_at_de.hellmann.net (joerg bruewer) wrote in message news:<d7c67a27.0206062131.25861f0c_at_posting.google.com>...
> I run a select-statement like this:
>
> select count(*) from a
> where not exists (select 1 from b
> where b.qualifier = 'XX'
> and a.id = b.id)
>
> in v$session i can see, that the session is 'sleeping'.
> It takes only a few blocks an no resources.
>
> Oracle 8.1.7.2 on Linus SusE SE7

What does Oracle say the session is waiting on in v$session_wait?

But since an exists is executed once for every row in the outer query I think you would get better performance from a join:

select count(*)
from a, b
where a.key = b.key
and b.qualifier != 'XX'

HTH -- Mark D Powell -- Received on Fri Jun 07 2002 - 08:46:08 CDT