For testing unnecessary large table full table scans, try a rule hint (select /*+ RULE */ col1). If the query uses the index with a rule hint, you have an issue with the CBO.”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve.

Actions

Information

7 responses

6122010

fidelinho(16:34:06) :

> If the query uses the index with a rule hint, you have an issue with the CBO.
he should try to look at any SAP ERP system
Most of the index start with the field MANDT.
Most of the queries get the field automagically added by the DBI.
Rule #8 will kick in and the index used.
This is one of the historical reasons why the field is added to the index and the queries.
Most of the time the MANDT has 1 value.
RULE will use an index even when only the MANDT is on the index, even when the complete index is read and then the complete table, while the CBO will use a FTS in this case without any issue with the CBO

This question might be more difficult to answer than I first thought. To help you get started, consider the following paraphrase from the book “Practical Oracle 8i”:
“The RULE based optimizer will not use descending indexes, must make certain that the cost based optimizer is used.”

At first that might seem like an odd problem, and kind of makes you wonder what the rule of thumb is for function based indexes, bitmap indexes, bitmap joins of B*tree indexes, partitioning, parallel query, table join order, etc. What does AskTom say?

Roughly 10 people looked at the AskTom article, but no replies. So, I thought that I would provide a short demonstration why it is a bad, and probably a silly idea to suggest that people use a RULE hint. If you check the hint reference for Oracle Database 11.2 I believe that you will find that the hint is no longer listed.

Creating a simple table for the first set of testing (Oracle Database 11.2.0.1):

In this case, the cost based optimizer will permit the descending index on column C2 to be used, while the RULE based optimizer will not – the book is silent on what to do if fewer indexes are used when the RULE based optimizer is used.

This time the cost based optimizer selects to use two of the bitmap indexes, while the RULE based optimizer decides to perform a full table scan. In addition to not being able to utilize descending indexes, the RULE based optimizer is not able to use bitmap indexes.

Once again the cost based optimizer decided to use an index, in this case a function based index (descending indexes are also function based indexes), while the RULE based optimizer decided to use a full table scan because it cannot use function based indexes.

It is good to see that the query, even with the RULE hint, is using the index on column C1, but wait, Cost figures do not display when the RULE optimizer mode is used, and also note that the warning in the Note section did not print – the cost based optimizer was used even with the RULE hint because of the default parallel degree specified for the table.

What does that mean? What is an unnecessary scan? Obviously with a selectivity of 1 you can expect a full scan. With a selectivity approaching 0 you are more likely to see an index but no guarantees. It would seem that if you have labeled something as “unnecessary” then you have already passed judgement on the CBO and know you “have an issue” with it.

Version support aside, once you start to intervene, you have to be very careful and try to do the least intervention possible and it would seem that more modern solutions like:

* Histograms
* DYNAMIC_SAMPLING hint

should be entertained before suggesting a hint like that. At least those solutions adapt to changes in the data where some other hints do not. Any more I have very few problems with Oracle getting single table access correct and for most data, it’s pretty good at getting multiple tables right. My focus is to help Oracle get the cardinality estimate right (thanks Jonathan and Wolfgang).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: