Making A "Hash" of it. Why are indexes not used?

From: Stuart MacKinnon <applicationz_at_paradise.net.nz>
Date: 25 Feb 2004 11:10:27 -0800
Message-ID: <56db38fe.0402251110.5f27cf4e@posting.google.com>

Hi all,
Using 8i, I have a complex query involving 2 views (one nested inside the other).
The results are then transposed using "decode" in a third view "SXV_LP_TRANSACTIONS_TOTALS". If I use Query 1 (see below), I get the results back in a few seconds. If I use Query 2, the performance is so bad that I cancel it after 10 minutes with no results.
The only difference is that in Query 1, I supply the "ADMISSION_SEQU's" in the Where clause, but in Query 2, I supply the "ADMISSION_SEQU's in a lookup table which contains the same four values".

All of the many joins involved in both queries have indexes available (Including the single column in SXT_LP_ADM_LOOKUP).

When I look at the explain plan, Query 1 has a cost of 33, and the plan shows the use of 9 nested loops and a merge join. Query 2 on the other hand has a cost of over 800 and the plan shows a few nested loops, 6 Hash joins, and instead of using all indexes, there are four cases of "Full" table scans.

The database has had statistics run recently so I wonder why Oracle gets it right in Query 1, and Soooooo wrong in Query 2.

I want to use SXV_LP_TRANSACTIONS_TOTALS in a variety of reports but right now it is useless.

Any advice welcome!

Regards,
Stuart MacKinnon
Auckland
New Zealand

---

Query 1

---

SELECT
*
FROM
SXV_LP_TRANSACTIONS_TOTALS
WHERE
SXV_LP_TRANSACTIONS_TOTALS.ADMISSION_SEQU IN (173883, 179618, 186926, 187725);

---

Query 2

---

SELECT
*
FROM
SXV_LP_TRANSACTIONS_TOTALS,
SXT_LP_ADM_LOOKUP
WHERE
SXV_LP_TRANSACTIONS_TOTALS.ADMISSION_SEQU = SXT_LP_ADM_LOOKUP.ADMISSION_SEQU;

---

Received on Wed Feb 25 2004 - 13:10:27 CST