Re: Improve performance on below query.

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.