Examine the Exhibit to view the structure of an indexes for the SALES table.
The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values.
What would you do to influence the optimizer for better selectivity?
A.
Drop bitmap index and create balanced B*Tree index on the CUST_ID column.
B.
Create a height-balanced histogram for the CUST_ID column.
C.
Gather statistics for the indexes on the SALES table.
D.
Use the ALL_ROWS hint in the query.
Explanation:
OPTIMIZER_MODE establishes the default behavior for choosing an optimization
approach for the instance. Values: FIRST_ROWS_N – The optimizer uses a cost-based approach
and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100,
1000). FIRST_ROWS – The optimizer uses a mix of costs and heuristics to find a best plan for fast
delivery of the first few rows. ALL_ROWS – The optimizer uses a cost-based approach for all SQL
statements in the session and optimizes with a goal of best throughput (minimum resource use to
complete the entire statement).
For me, correct answer is “B”
D seems logical. You’re doing (at least – if each bitmap index block occupies only one block) 28 single block reads to read the index on all partition. You might as well do a Full Table Scan; that’s a multi block read and perhaps faster => ALL_ROWS
I think C is correct because estimation for row cardinality is a little too low.