Re: Intersect vs. Join

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F4B9249.B2DA836F_at_exxesolutions.com>...
> Lavanya wrote:
>
> > Hi,
> >
> > I have the following query
> >
> > (select IP_ID from IP where upper_last_nm like 'M%')
> > intersect
> > (select IP_ID from feed_ip_alias where extrnl_sys_cd = 'DART' and
> > extrnl_org_no = '418')
> >
> > The first query pulls out 52674 records.
> > The second query puuls out 25324 records.
> >
> > Both the columns have indexes and the explain plan looks like this
> > SELECT STATEMENT Cost =
> > 1.1 INTERSECTION
> > 2.1 SORT UNIQUE
> > 3.1 TABLE ACCESS BY INDEX ROWID IP
> > 4.1 INDEX RANGE SCAN IP_UPPER_LAST_NM_I
> > 2.2 SORT UNIQUE
> > 3.1 TABLE ACCESS BY INDEX ROWID FEED_IP_ALIAS
> > 4.1 INDEX RANGE SCAN FEEDIP_PK
> >
> > The above query takes 127 sec.
> >
> > Another problem is after I run this query once it caches data and
> > runs very fast next time.
> >
> > Questions
> > 1) Can this sql be re-written to improve performance?
> >
> > 2) Is there any way to clear all caches so that I can have
> > consistant perforamce timings and know when i actually tuned the query
> > vs. when it is giving an illusion due to cache?
> >
> > Thank you
> > Lavanya
>
> A few things first.
>
> 1. It is obvious from the lack of a cost value that you have not run
> DBMS_STATS for the optimizer. Do so and rerun your tests.
>
> 2. You should not test against the unrealistic situation where data is not
> cached. Far more realistic to run a statement several times and take the
> performance when the database is closer to real-world.

In real world also sometimes it is very slow and times out. I want to make sure this doesn't happen. I have modified the sql as follows

SELECT
count(*) AS COUNT
FROM
feed_ip_alias fiaid,
 ip ip_
WHERE fiaid.ip_id = ip_.ip_id
AND fiaid.extrnl_sys_cd = 'DART'
AND fiaid.extrnl_org_no = '418'
AND ip_.upper_last_nm LIKE 'MAT%'
AND ip_.client_status_ty_cd in ('ACTV','PEND') AND ROWNUM <= 1600

CHOOSE SELECT STATEMENT Cost = 20

1.1     SORT AGGREGATE
2.1       COUNT STOPKEY
3.1         NESTED LOOPS
4.1           TABLE ACCESS BY INDEX ROWID FEED_IP_ALIAS
5.1             INDEX RANGE SCAN FEEDIP_PK
4.2           TABLE ACCESS BY INDEX ROWID IP
5.1             INDEX UNIQUE SCAN IP_PK

Although the count is only 48 it takes 65 secs. Received on Tue Aug 26 2003 - 17:48:29 CDT