Like Wikipedia, the Oracle documentation is a great place to start. It provides a lot of information, and a lot of 'facts'. However, also like Wikipedia, blind faith in the documentation as an absolute source truth can occasionally be misplaced.
My response to that thread was
I like Tom Kyte's attitude around this, which can be paraphrased as "it ain't true until you've proven it for yourself - and even then, recheck periodically"
In an engine such as Oracle, where things can automatically adjust themselves based on statistics, it is probably unwise to take things like '20% or 20 blocks' as an absolute.
For example, consider the following: "When is it better to do a full table scan instead of using an index that is referenced in the query?"SQL is designed around the idea that we declare what is to be accomplished, not how it is to be accomplished. The Oracle Server database engine is free to interpret the request in a variety of ways, and come up with different ways to implement the response.
We will often say "when you get back 'N'% of the data", and 'N' will be typically between 10 and 25%.
Once the different code paths have been determined, the optimizer can apply different weight to each path based on the cost of doing that, the number of rows involved, and so on.
However, a much more accurate answer would be "when the optimizer has determined the total cost of the table scan to be lower than the total cost of using the index". And factors that could adjust that cost might include:For years, Oracle Tuning courses have emphasized that there are only two ways to speed things up - reduce the time waiting on resources, and reduce the time working toward the end result. Over time the designers and developers of the Oracle engine have collected a lot of information about how things are done and where the bottlenecks occur. Some of that research has resulted in new code paths, but some has resulted in interpreting the statistics in new ways, of changing the weight of specific code paths - making more efficient choices.
- current statistics on the table and the column
- whether the data in the column is required for any subsequent operation
- whether the column is involved as an expression in the output
which could possibly change the percentages in the answer from 5% to 50%.
As the optimizer has been improved, it has added some quite unexpected capabilities but it has also added some interesting 'opportunities for improvement' and side effects.The documentation is a great, and even a mandatory, starting place to understand Oracle. However, there are several points to keep in mind, highlighted by my colleagues Joel Garry and Justin Cave:
- The documentation is written by humans and can easily have some errors;
- The documentation is published periodically and may not reflect each update that has occurred in the rush to deliver product;
- The documentation normally does not reflect behaviour due to bugs;
- The documentation can not reflect every possible decision-tree variant chosen due to statistics
- 90% of the time the concepts and the general ideas that are provided in the docs are good enough to use unchanged
- 9% of the time, the docs are a great springboard to move you in your own direction, if you have your own tests. A rule of thumb for this category is when the docs provide a number, and no supporting script or test.
- 0.9% of the time, the docs may be completely insufficient, and you need to turn to My Oracle Support or to experts like Jonathan Lewis or Tanel Poder who are willing to share their knowledge. However, this is beyond the normal day-to-day DBA activity and goes into the realm of 'very interesting stuff'.
- 0.009% of the time, you have the opportunity to become an expert yourself.
- And perhaps for 0.0009% of the time (if that high), the docs have mistakes - which you should report to the Documentation forum at http://forums.oracle.com/
Don't give up on the documentation.