Re: Intersect vs. Join

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Tue, 26 Aug 2003 17:04:10 -0700
Message-ID: <3F4BF579.82B0FDF@exxesolutions.com>

Lavanya wrote:

> 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.

If something, in the real-world situation is slow and times-out ... but in a cold-start test works you have got so many problems I'd suggest you bring in a consultant.

--
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)

Received on Tue Aug 26 2003 - 19:04:10 CDT