Oracle Common Misconceptions

comSAP Note 825653 Note Language: English

Oracle: Common misconceptions

Version: 77 Validity:

Valid Since 24.05.2011

Summary

Symptom

Some widely accepted information about the Oracle database is shown not to be entirely correct upon closer examination.

Other terms Reason and Prerequisites

The following list contains statements that are not entirely correct.

Solution

1. The Oracle cost-based optimizer always selects the access with the lowest calculated costs. This is not always true since the cost-based optimizer also takes rule-based decisions in certain situations. For example, sometimes the optimizer does not use an access with costs that are a hundred times better, because the first index column is not specified with "=" in the WHERE condition and the access is therefore judged as a "Guess". For more information, see Note 176754. 2. Indexes should be set up in a way that the selection fields are located at the beginning of the index. It is much more important that fields that are not specified with "=" in WHERE conditions and that cover a large value range are located as near to the end of the index as possible. In addition, the index should not contain any gaps that are not specified in the WHERE condition. See Note 766349 also. If these recommendations are considered and if you can still choose any field sequence (in part), it is usually better to place nonselective fields at the top since their values tend to change less often in several query executions and are always processed in the same index subtree as a result. You can use the index compression better also. 3. Indexes that only index columns with few instances are superfluous. In many cases, you cannot equate the number of distinct values in a column with the selectivity of certain conditions in this column. Often, conditions for columns with few instances are very selective. Therefore, indexes in columns with few distinct values frequently considerably improve the performance. See...