Re: Improve performance on below query.

Yet another, but more obscure way is:

select count(*)
from
 woa.serv_ord s, woa.so_fld_ord_rao f
where s.ky_so_no = f.ky_so_no(+)
and f.rowid is null

This query outer joins the two tables, and only retrieves the rows that effectively have been outer joined (where rowid is null).

You may give it a try.

Marc

James Williams wrote in message <39a3deb3.7977368_at_news.mindspring.com>...
>Below is a query I need to get to perform better. It is using a lot of
>resources during fetch. ky_so_no on both tables has an index. Any
>hints!
>
>
>select count(*)
>from
> woa.serv_ord where ky_so_no not in ( select ky_so_no from
>woa.so_fld_ord_rao)
>
>
>
>call count cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.00 0.00 0 0 0
>0
>Execute 1 0.00 0.00 0 0 0
>0
>Fetch 1 666.59 667.68 336598 3625877 26201
>0
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 3 666.59 667.68 336598 3625877 26201
>0
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 5
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 0 SORT AGGREGATE
> 0 FILTER
> 6549 TABLE ACCESS FULL SERV_ORD
> 6548 TABLE ACCESS FULL SO_FLD_ORD_RAO
>
>***************************************************************************