1Z0-117 · Question #9
You are administering a database supporting an OLTP application. The application runs a series of extremely similar queries the MYSALES table where the value of CUST_ID changes. Examine Exhibit1 to…
The correct answer is D. Collect histogram statistics for the CUST_ID column and use a bind variable instead of literal values. Using Histograms In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a…
Question
You are administering a database supporting an OLTP application. The application runs a series of extremely similar queries the MYSALES table where the value of CUST_ID changes. Examine Exhibit1 to view the query and its execution plan. Examine Exhibit 2 to view the structure and indexes for the MYSALES table. The MYSALES table has 4 million records. Data in the CUST_ID column is highly skewed. Examine the parameters set for the instance:
Which action would you like to make the query use the best plan for the selectivity?
Exhibits
Options
- ADecrease the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
- BUs the /*+ INDEX(CUST_ID_IDX) */ hint in the query.
- CDrop the existing B* -tree index and re-create it as a bitmapped index on the CUST_ID column.
- DCollect histogram statistics for the CUST_ID column and use a bind variable instead of literal values.
How the community answered
(24 responses)- A4% (1)
- B4% (1)
- C13% (3)
- D79% (19)
Explanation
Using Histograms In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access. A column histogram should only be created when we have data skew exists or is
Topics
Community Discussion
No community discussion yet for this question.