Re: Intersect vs. Join

a260098_at_fmr.com (Lavanya) wrote

My few cents's worth of opinion.

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

Two comments.
- Both queries make use (according to your execution plan) of   index range scans. This can at times be a Bad Thing (tm). - The 2nd query has a stopkey (i.e. rowcount). You cannot use   that execution time as a comparison with query 2

As Daniel mentioned, the CBO has no stats to go on. This can (and will) result in less than optimal execution plans. The CBO can be pretty smart at times.. if given the chance.

As for which method is the fastest. That's open to speculation unless on can do some hands-on testing to bake the pudding for the proof.

On the speculation side. The intersection method will be fast if the two data sets to intersect are relatively small. The bigger the data sets, obviously the more time to spend on determining the intersection.

The index range scans could be candidates for fast full index scans or full table scans - either can be done in parallel which could provide significant increase in performance. (but PQ performance is primarily a function of data volumes - yours could be to low to warrant using PQ)

Your execution plans shows 2 x index range scans in the intersection query versus 1 x index range scan + 1 x unique index scan in the join criteria. All things equal, a unique scan is a lot faster than an index range scan.

Bottom line. There's seldom a single best way to do a thing in Oracle. It almost always depends on data volumes, indexing and so on. Intersection has its merrits and so does joins. The trick is to determine which is the best under the given circumstances... and that is only something you can determine hands-on.

Thus keep plugging away as you're doing. Just remember that stats makes a difference and that you can force Oracle to try other execution paths too via hints. (i.e. the answer to your question is not be found here, but there waiting to be discovered in your database)

--
Billy