Why would the optimizer choose full table scan over index unique scan?

Examine the initialization parameter values for the instance given below:
NAME TYPE VALUE
———————————— ———– ——————
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
db_file_multiblock_read_count integer 64

You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index unique scan (view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. The cost for a full table scan is more than that for an index unique scan.

Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)

Examine the initialization parameter values for the instance given below:
NAME TYPE VALUE
———————————— ———– ——————
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer                        2
optimizer_features_enable string                    11.1.0.6
optimizer_index_caching integer                              0
optimizer_index_cost_adj integer                          100
optimizer_mode string                             ALL_ROWS
db_file_multiblock_read_count integer                     64

You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index unique scan (view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. The cost for a full table scan is more than that for an index unique scan.

Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)

Exhibit 1 (exhibit):

Exhibit 2 (exhibit):

A.
The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a low value.

B.
The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.

C.
The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a low value.

D.
The statistics for the table and all the indexes associated with the table are not current.



Leave a Reply 0

Your email address will not be published. Required fields are marked *