Re: Merge Join (Cartesian)

Originally posted by Ty
> I have a perplexing problem involving cartesian joins (Oracle
> 8.1.7.4). The following query will run in around a minute or so:
>
> SELECT nci.health_service_id, nci.i_product_id /* there is an index on
> these two columns */
> FROM ncpdp_claim_item nci, claim c, drug_clinical dc, tlp t
> WHERE nci.i_product_id = dc.product_id
> AND dc.jurisdiction_cd = ' '
> AND dc.rec_eff_dt_comp = (SELECT MIN(rec_eff_dt_comp)
> FROM drug_clinical
> WHERE jurisdiction_cd = ' '
> AND product_id = dc.product_id
> AND rec_inactive_flag = 'A')
> AND dc.seq_no = (SELECT MIN(seq_no)
> FROM drug_clinical
> WHERE jurisdiction_cd = ' '
> AND product_id = dc.product_id
> AND rec_inactive_flag = 'A'
> AND rec_eff_dt_comp = dc.rec_eff_dt_comp)
> AND nci.health_service_id = c.health_service_id
> AND nci.health_service_id = t.hsn
>
> The explain plan is:
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=873 Card=2 Bytes=142)
> FILTER
> NESTED LOOPS (Cost=873 Card=2 Bytes=142)
> HASH JOIN (Cost=871 Card=2 Bytes=120)
> INDEX (FAST FULL SCAN) OF DRUG_CLINICAL_MIDX03 (NON-UNIQUE)
> (Cost=4 Card=1 Bytes=29)
> NESTED LOOPS (Cost=866 Card=24658 Bytes=764398)
> INDEX (FAST FULL SCAN) OF NCPDP_CLAIM_ITEM_MIDX02
> (NON-UNIQUE) (Cost=866 Card=2755100 Bytes=57857100)
> INDEX (UNIQUE SCAN) OF TLP_MIDX01 (UNIQUE)
> INDEX (UNIQUE SCAN) OF CLAIM_IDX1 (UNIQUE) (Cost=1 Card=2754328
> Bytes=30297608)
> SORT (AGGREGATE)
> FIRST ROW (Cost=2 Card=2 Bytes=48)
> INDEX (RANGE SCAN (MIN/MAX)) OF DRUG_CLINICAL_MIDX04
> (NON-UNIQUE) (Cost=2 Card=2)
> SORT (AGGREGATE)
> FIRST ROW (Cost=2 Card=1 Bytes=31)
> INDEX (RANGE SCAN (MIN/MAX)) OF DRUG_CLINICAL_MIDX04
> (NON-UNIQUE) (Cost=2 Card=1)
>
> I now alter the query by adding a column from the same table to the
> select list:
>
> SELECT nci.health_service_id, nci.i_product_id, nci.i_rx_no /* there
> is no index on these three columns */
> FROM ncpdp_claim_item nci, claim c, drug_clinical dc, tlp t
> WHERE nci.i_product_id = dc.product_id
> AND dc.jurisdiction_cd = ' '
> AND dc.rec_eff_dt_comp = (SELECT MIN(rec_eff_dt_comp)
> FROM drug_clinical
> WHERE jurisdiction_cd = ' '
> AND product_id = dc.product_id
> AND rec_inactive_flag = 'A')
> AND dc.seq_no = (SELECT MIN(seq_no)
> FROM drug_clinical
> WHERE jurisdiction_cd = ' '
> AND product_id = dc.product_id
> AND rec_inactive_flag = 'A'
> AND rec_eff_dt_comp = dc.rec_eff_dt_comp)
> AND nci.health_service_id = c.health_service_id
> AND nci.health_service_id = t.hsn
>
> The explain plan is:
>
> SELECT STATEMENT Optimizer=CHOOSE (Cost=17528 Card=2 Bytes=152)
> FILTER
> NESTED LOOPS (Cost=17528 Card=2 Bytes=152)
> NESTED LOOPS (Cost=17526 Card=2 Bytes=130)
> MERGE JOIN (CARTESIAN) (Cost=6 Card=5840 Bytes=227760)
> INDEX (FAST FULL SCAN) OF DRUG_CLINICAL_MIDX03 (NON-UNIQUE)
> (Cost=4 Card=1 Bytes=29)
> SORT (JOIN) (Cost=2 Card=5840 Bytes=58400)
> TABLE ACCESS (FULL) OF TLP (Cost=2 Card=5840 Bytes=58400)
> TABLE ACCESS (BY INDEX ROWID) OF NCPDP_CLAIM_ITEM (Cost=3
> Card=2755100 Bytes=71632600)
> INDEX (RANGE SCAN) OF NCPDP_CLAIM_ITEM_MIDX02 (NON-UNIQUE)
> (Cost=2 Card=2755100)
> INDEX (UNIQUE SCAN) OF CLAIM_IDX1 (UNIQUE) (Cost=1 Card=2754328
> Bytes=30297608)
> SORT (AGGREGATE)
> FIRST ROW (Cost=2 Card=2 Bytes=48)
> INDEX (RANGE SCAN (MIN/MAX)) OF DRUG_CLINICAL_MIDX04
> (NON-UNIQUE) (Cost=2 Card=2)
> SORT (AGGREGATE)
> FIRST ROW (Cost=2 Card=1 Bytes=31)
> INDEX (RANGE SCAN (MIN/MAX)) OF DRUG_CLINICAL_MIDX04
> (NON-UNIQUE) (Cost=2 Card=1)
>
> Adding the column to the select list causes a cartesian join and the
> query will run for hours before it finally fails with ORA-01555
> "Snapshot too old". It seems odd to me that adding a column from the
> same table to the select list could have such a devasting effect on
> performance. Have any of you gurus seen anything like this? Oracle
> support has told me that this is a normal sql tuning issue. Perhaps
> it is, but I would still like to know what is going on here...
>
> Thanks for the help,
> Ty
> housequake_at_yahoo.com

It is not perplexing at all. As you noted yourself, in the first query, all the columns in the select are indexed and Oracle therefore only needs to read the index. When you added a column to the select list that is not in the index, Oracle has no choice but access the table, so the access plan necessarily has to change. Whether the access plan for the second sql can be improved (most likely) is a different matter.

--
Posted via http://dbforums.com