Re: Improve performance on below query.

On Thu, 24 Aug 2000 07:59:39 +0200, "Marc Billiet" <E.Mail_at_address.com> wrote:

subsecond on 150 ,000 rows.

>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
>>
>>***************************************************************************