What can you do to make the optimizer detect the actual number of rows?

View Exhibit1 to examine the description of the CUSTOMERS table.

You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and
COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to
examine the query execution plan and the commands executed to gather the statistics.

The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the
actual number of rows returned from the table. What can you do to make the optimizer detect the
actual number of rows?

View Exhibit1 to examine the description of the CUSTOMERS table.

You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and
COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to
examine the query execution plan and the commands executed to gather the statistics.

The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the
actual number of rows returned from the table. What can you do to make the optimizer detect the
actual number of rows?

A.
Set the STATISTICS_LEVEL parameter to ALL.

B.
Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.

C.
Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns.

D.
Increase the STALE_PERCENT value for the CUSTOMERS table by using the
DBMS_STATS.SET_TABLE_PREFS procedure.



Leave a Reply 1

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


L. Zhu

L. Zhu

country and province has relationship

dbms_stats.create_extended_stats will help to create virtual column and then collect stats, including histogram on the newly created virtual column for selectivity

So C is correct