Home » Oracle » 1z0-117 » What can be the two reasons for full table scan?
Examine the initializing parameters:
An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The CUSTOMERS table has 55000 rows.
View the exhibit and examine the query and its execution plan.
What can be the two reasons for full table scan?
Exhibit:
A. The value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.
B. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
C. The statistics for the CUSTOMERS table and the indexes stale.
D. The OPTIMIZER_MODE parameter is set to ALL_ROWS.
E. Histogram statistics for CUST_CITY_ID are missing.
F. Average number of rows per block for the CUSTOMERS table is low.
Correct Answer: CD
Explanation/Reference:
C: Old statistics could cause this problem.
D:Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order.
Incorrect:
A: 100 is the maximum value ofOPTIMIZER_INDEX_COST_ADJ
Note:OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
B:DB_FILE_MULTIBLOCK_READ_COUNTdoes not apply:
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
F: High (not low) row per block could make a table scan preferable.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
