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…

The correct answer is D. Use the ALL_ROWS hint in the query. OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for 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…

Understanding and Influencing the Optimizer

Question

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?

Exhibit

Options

ADrop bitmap index and create balanced B*Tree index on the CUST_ID column.
BCreate a height-balanced histogram for the CUST_ID column.
CGather statistics for the indexes on the SALES table.
DUse the ALL_ROWS hint in the query.

How the community answered

(42 responses)

A
2% (1)
B
7% (3)
C
17% (7)
D
74% (31)

Explanation

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for 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 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

Topics

#histogram statistics#optimizer selectivity#ALL_ROWS hint#cardinality estimation

Community Discussion

No community discussion yet for this question.

Full 1Z0-117 Practice