View 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 have few distinct values relative to the total
number of rows in the table. The MYSALES table has 4.5 million rows.
View exhibit 2 and examine one of the queries and its autotrace output.
Which two methods can examine one of the queries and its autotrace output?
A.
Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns
and re-create as bitmapped indexes.
B.
Use the INDEX_COMBINE hint in the query.
C.
Create a composite index involving the CUST_ID and PROD_ID columns.
D.
Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the
value for PCTFRE attribute.
E.
Collect histogram statistics for the CUST_ID and PROD_ID columns.
Explanation:
B: The INDEX hint explicitly chooses an index scan for the specified table. You can
use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle
recommends using INDEX_COMBINE rather than INDEX for the combination of multiple indexes,
because it is a more versatile hint.
C: Combining the CUST_ID and PROD_ID columns into an composite index would improve
performance.
For me “C” is incorrect because MYSALES_PRODIS_IDX is already a composite index.
For me “A” and “B” are correct.
“A” by using a Bitmap directly, we prevent conversion from rowid -> bitmap and bitmap -> rowid.
http://ahmedaangour.blogspot.be/2011/07/bitmap-conversion-from-rowids.html
“B” The index_combine hint is used to force a bitmap access path for the table, where miltiple bitmap indexes exist for the table. If no indexes are given as arguments for the index_combine hint, the optimizer will choose whatever Boolean combination of bitmap indexes has the best costing estimate for the table access. The index_combine hint directs the optimizer to perform a ROWID intersection operation from both bitmaps. In practice, it is always a good idea to specify the table name and both index names in the hint.
http://www.dba-oracle.com/t_index_combine_hint.htm
The answer A doesn’t seem to be correct.
Because sales table is used in OLTP system, and bitmap index could lead to overlocking.
“columns have few distinct values relative to the total number of rows” => implement Bitmap Index! => A
THEN you’ll need to instruct Optimizer to use those indexes => INDEX_COMBINE
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#BABHJBAJ
=> B
A is not correct because we have OLTP system
B is not correct because INDEX_COMBINE is for bitmap indexes
C is not correct because we have already composite index
D and E seem to be correct. Wrong plans can be caused by huge cost of acces by index (high value of cluster factor or blevels). Also collect histogram statistics can help is some cases.
I’m sorry, where is OLTP in question ?
A,C are correct (see question 30 of this exam, exhibit in this is “damaged”)