Home » Oracle » 1z0-117 » Which three scenarios can prevent the index access being used by the queries?
You are administering database that supports an OLTP workloads. Most of the queries use an index range scan or index unique scan as access methods.
Which three scenarios can prevent the index access being used by the queries?
A. When highly selective filters is applied on an indexed column of a table with sparsely populated blocks.
B. When the rows are filtered with an IS NULL operator on the column with a unique key defined
C. When the histogram statistics are not collected for the columns used in where clause.
D. When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.
E. When the statistics for the table are not current.
Correct Answer: BDE
Explanation/Reference:
D:The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for optimal access.
Note:
*Oracle SQL not using an index is a common complaint, and it’s often because the optimizer thinks that a full-scan is cheaper than index access. Oracle not using an index can be due to:
*(E)Bad/incomplete statistics - Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.
*Wrong optimizer_mode - The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.
*Bugs - See these important notes on optimizer changes in 10g that cause Oracle not to use an index.
*Cost adjustment - In some cases, the optimizer will still not use an index, and you must decrease optimizer_index_cost_adj.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
