Re: Improve performance on below query.

James Williams wrote:
>
> 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.

Possibly try MINUS

select * from tab1
minus
select * from tab2

and also have a look at some of the anti-join hints.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse