Home » Oracle » 1z0-117 » Which three statements are true about histograms?
Which three statements are true about histograms?
A. They capture the distribution of different values in an index for better selectivity estimates.
B. They can be used only with indexed columns.
C. They provide metadata about distribution of and occurrences of values in a table column.
D. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.
E. They help the optimizer in deciding whether to use an index or a full table scan.
F. They help the optimizer to determine the fastest table join order.
Correct Answer: CEF
Explanation/Reference:
C:A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.
E:It’s well established that histograms are very useful for helping the optimizer choose between a full-scan and and index-scan.
F:Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order.
For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order.
Note:
*The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.
Incorrect:
B:Histograms are NOT just for indexed columns.
- Adding a histogram to an un-indexed column that is used in a where clause can improve performance.
D:Histograms Opportunities
Any column used in a where clause with skewed data
Columns that are not queried all the time
Reduced overhead for insert, update, delete
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
