Re: Intersect vs. Join

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.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)