Re: Improve performance on below query.

From: James Williams <techsup_at_mindspring.com>
Date: Wed, 23 Aug 2000 18:54:30 GMT
Message-ID: <39a41d93.24075991@news.mindspring.com>

oratune_at_aol.com wrote:

---

SELECT count('1') FROM woa.serv_ord o
  WHERE NOT EXISTS (SELECT '1' FROM woa.so_fld_ord_rao i

                                         WHERE o.ky_so_no=i.ky_so_no)

call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 141.35 142.57 0 390220 4 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 3 141.36 142.58 0 390220 4 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation

-------  ---------------------------------------------------
      0  SORT AGGREGATE 
      0   FILTER 

    838 INDEX FAST FULL SCAN (object id 6120)     837 INDEX FULL SCAN (object id 7770)

Index is being used performance is better but not good enough. Using TOAD bstat and estat. Everything looks pretty good there. Reorged the tables to get rid of most extents.

Still looking!

>In article <8o0piu$bb4$1_at_news.ipartners.pl>,
> "Roman Zarzecki" <rzarzecki_at_gis.com.pl> wrote:
>>
>> "James Williams" <techsup_at_mindspring.com> wrote in message
>> news: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)
>>
>> Try this:
>> SELECT count('1') FROM woa.serv_ord o
>> WHERE NOT EXISTS (SELECT '1' FROM woa.so_fld_ord_rao i
>> WHERE o.ky_so_no=i.ky_so_no);
>>
>>
>
>You should find the above query to be more efficient than your original.
> As you have, no doubt, discovered NOT IN does not use indexes; NOT
>EXISTS, on the other hand, utilizes a join which CAN use available
>indexes. NOT IN forces a full table scan which makes it one of the most
>inefficient methods of restricting the query result set. The WHERE
>clause in the NOT EXISTS subquery triggers the index for that
>subquery and the NOT EXISTS construct joins the results from the
>subquery to the driving table, making index access for the driving table
>possible. This reduces the work Oracle must do to return the result
>set since index scans are far more efficient than table scans for large
>volumes of data (yes, for a small table the reverse can be true). So,
>next time, try using NOT EXISTS instead of NOT IN.
>
>--
>David Fitzjarrell
>Oracle Certified DBA
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed Aug 23 2000 - 13:54:30 CDT