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

On 25 Feb 2004 11:10:27 -0800, applicationz_at_paradise.net.nz (Stuart MacKinnon) wrote:

>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;
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using CBO the driving table has to be the leftmost table. You are sticking to RBO conventions and have it the other way around. As you have no predicates on the 'driving' table, you are forcing a full table scan.
You might want to retry it with
alter session set optimizer_index_caching = 80 <anything that resembles your hit ratio>
and
alter session set optimizer_index_cost_adj to anything below 100 as most people complaining about CBO have these 2 parameters still on their default.
Other than that you could try
alter session set events="10053 trace name context forever, level 1" to produce a trace file stating how the CBO arrives at your 'erroneous' execution path.
I would suspect, because you have hardcoded values in the first query, it will implicitly use the USE_CONCAT scenario, and implicitly use the correct 'driving' table, and consequently the FK index. Finally you might want to experiment with histograms on the indexed column in the non-driving table.

--
Sybrand Bakker, Senior Oracle DBA