Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Intersect vs. Join

Intersect vs. Join

From: Lavanya <a260098_at_fmr.com>
Date: 26 Aug 2003 09:22:48 -0700
Message-ID: <74fef262.0308260822.676c9106@posting.google.com>


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
Received on Tue Aug 26 2003 - 11:22:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US