| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Making A "Hash" of it. Why are indexes not used?
Replacing an IN list with a loaded table can have this effect because the cost of the (expected) nested loop is
cost of reading driving table +
(
estimated number of rows in driving table *
cost of doing the original query once
)
If your original query cost was 33, you would expect new query to cost:
1 (cost of very small tablescan) +
4 * 33
However, it may be that Oracle has estimated the driving table as holding (say) 30 rows, in which its nested loop cost would be:
1 +
30 * 33
Hence the hash join path taken at a cost of over 800
try a hinted path to see if this guess is correct:
select {list of cols}
/*+ ordered use_nl(view_name) */ from
driving_table,
view
If this gives you the expected path, check the cost of the driving tablescan, and the cardinality (predicted number of rows) for the driving tablescan.
The hints may not work because there may be something in the views that forces the hints to go out of context and become ignorable, though.
It would be useful to know which version of 8i you have - to 4 digits.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: March 2004 Hotsos Symposium - The Burden of Proof DYnamic Sampling - an investigation March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February ____UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Stuart MacKinnon" <applicationz_at_paradise.net.nz> wrote in message news:56db38fe.0402251110.5f27cf4e_at_posting.google.com...Received on Sat Feb 28 2004 - 04:23:36 CST
> 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;
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 |
 |