Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Intersect vs. Join
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
Thank you LavanyaReceived on Tue Aug 26 2003 - 11:22:48 CDT