1Z0-117 · Question #118
View the Exhibit1 and examine the structure and indexes for the MYSALES table. The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating…
The correct answer is D. Gather index statistics for the MYSALES_PRODID_IDX and MYSALES_CUSTID_IDX indexes. Statistics quantify the data distribution and storage characteristics of tables, columns, indexes, Forces a bitmap index access path on tab. Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of…
Question
View the Exhibit1 and examine the structure and indexes for the MYSALES table. The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns are frequently used in WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. View exhibit 2 and examine one of the queries and its auto trace output. What should you do to improve the performance of the query?
Exhibits
Options
- AUse the INDEX_COMBINE hint in the query.
- BCreate composite index involving the CUST_ID and PROD_ID columns.
- CGather histograms statistics for the CUST_ID and PROD_ID columns.
- DGather index statistics for the MYSALES_PRODID_IDX and MYSALES_CUSTID_IDX indexes.
How the community answered
(36 responses)- A17% (6)
- B3% (1)
- C6% (2)
- D75% (27)
Explanation
- Statistics quantify the data distribution and storage characteristics of tables, columns, indexes, Forces a bitmap index access path on tab. Primarily this hint just tells Oracle to use the bitmap indexes on table tab. Otherwise Oracle will choose the best combination of indexes it can think of based on the statistics. If it is ignoring a bitmap index that you think would be helpful, you may specify that index plus all of the others taht you want to be used. Note that this does not force the use of those indexes, Oracle will still make cost based choices. * Histograms Opportunities Any column used in a where clause with skewed data Histograms are NOT just for indexed columns. ?Adding a histogram to an un-indexed column that is used in
Topics
Community Discussion
No community discussion yet for this question.