View the Exhibit 1.
In the CUSTOMERS_OBE table, when the value of CUST_STATE_PROVINCE is “CA”, the
value of COUNTRY_ID is “US.”
View the Exhibit exhibit2 to examine the commands and query plans. The optimizer can
sense 8 rows instead of 29 rows, which is the actual number of rows in the table.
What can you do to make the optimizer detect the actual selectivity?
desc-customers_obe (exhibit):
sql-select-customers_obe (exhibit):
A.
Change the STALE_PERCENT value for the CUSTOMERS_OBE table.
B.
Set the STATISTICS_LEVEL parameter to TYPICAL.
C.
Create extended statistics for the CUST_STATE_PROVINCE and CUSTOMERS_OBE
columns.
D.
Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.
Explanation:
Real example refer to here.
Determining Single Column Statistics
A good example of correlated or related columns are the country_id and cust_state_province
columns in the CUSTOMERS_OBE table. When the value of cust_state_province is ‘CA’ ,
the value of country_id is ‘US’. There is a skew in the data in these two columns, which
means the majority of rows in the table have the values ‘CA’ and ‘US’. Both the relationship
between the columns and the skew in the data make it difficult for the optimizer to calculate
the selectivity or cardinality of these columns correctly when they are used together in a
query. Extended statistics should help in this situation.