Re: Lock-up query

Use not exists instead of the subquery
or include the /*+HASH_AJ(c) */ hint in it, providing you are using the Cost Based Optimizer (which you should use)

Hth,

Sybrand Bakker, Oracle DBA

"Mikael Carneholm" <arch_at_comitnet.com> wrote in message news:9a1f00$3asvf$1_at_ID-58279.news.dfncis.de...
> Hi,
>
> I wonder if someone has had the following problem:
>
> When selecting one attribute from a table, for example:
>
> select c.cnumber from customers c
>
> ...everything's fine. But when running a query that tries to do a
 difference
> using a subquery, like:
>
> select * from customertypes ct where ct.cnumber not in (
> select c.cnumber from customers c
> )
>
> ...the query hangs (had it running for ~3 hours before killing it).
 There's
> no disc swapping, but CPU usage goes up to ~98% (on a SUN Ultra10 running
> Solaris 8).
>
> Any ideas..?
>
> ---------------------------------------------------------------
> Mikael Carneholm
> Systems Developer
> Comitnet AB, Sweden
> mikael.carneholm_at_comitnet.com
> +46 33 101020
> ---------------------------------------------------------------
>
>
Received on Fri Mar 30 2001 - 03:29:24 CST