On Mar 19, 6:22 am, Javier Montani <[email protected]> wrote:
> You have to look for the FULL SCANS in tables and then create the
> corresponding indexes to avoid them. Be careful with this because the more
> indexes you have the worse the performance when the table grows.
>
Why? It could be that a full table scan is the most cost-effective
way to return the requested data:
http://oratips-ddf.blogspot.com/2007/06/table-scans-histograms-and-scarlett.html
Blindly suggesting that all full table scans are bad indicates you
don't fully understand the costing algorithm. If the query returns
most of the data in a table it's often 'cheaper' and more efficient
perform a full table scan and 'discard' the few rows which don't meet
the criteria rather than read an index and execute any number of
'table fetch by rowid' calls (which doubles the I/O for the query
since you have to read the index, then read the table). It's not
always the case that a table scan is 'bad'. Remember my example from
both prior posts: the latitude and longitude example. This is a real-
life situation I corrected in the manner I have already described and
the execution plan already listed index access for the table in
question. Looking at the plan alone would NOT have provided the
solution; it was the addition of the per-query statistics from
autotrace which gave the clue that Oracle was filtering on longitude
and the operation was consuming excessive resources. Creating a NEW
concatenated index was the solution, and, again, there was NO 'TABLE
ACCESS FULL' entry to 'fix'.
There are no 'silver bullets' in Oracle, and 'blanket' advice isn't
advice, it's most often misinformation. Unfortunately the half-truths
floating around for years are still used as gospel, and your idea that
full table scans are bad, without exception, is one of those. Please
learn that this idea (that full table scans are always bad) isn't
always true and there are situations where a full table scan is the
best way to retrieve the data.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
