When a query filters on two columns, the optimizer multiplies their selectivities, as if the columns were independent. Real data is often correlated: a city lies in one country, a car model belongs to one make, a postal code to one state. For such columns the multiplication underestimates the rows, sometimes by orders of magnitude. Extended statistics on a column group tell the optimizer how many distinct combinations really exist. This guide shows the misestimate, creates a column group, and covers the histogram detail that decides whether the optimizer actually uses it.
Syntax
select dbms_stats.create_extended_stats(user, 'TABLE_NAME', '(COLUMN1, COLUMN2)') from dual;
The function creates the column group and returns its generated name; statistics for it are collected by the next gathering on the table. Column groups are listed in USER_STAT_EXTENSIONS.
Sample Table
100,000 addresses in 100 cities, and each city lies in exactly one of 10 countries:
Example:
create table demo_addresses (
address_id number constraint demo_addresses_pk primary key,
city varchar2(20) not null,
country varchar2(20) not null);
-- 100 cities, each in exactly one of 10 countries
insert into demo_addresses
select level, 'City' || mod(level, 100), 'Country' || mod(mod(level, 100), 10)
from dual connect by level <= 100000;
commit;
exec dbms_stats.gather_table_stats(user, 'DEMO_ADDRESSES')
select column_name, num_distinct from user_tab_col_statistics
where table_name = 'DEMO_ADDRESSES' and column_name in ('CITY', 'COUNTRY');Output:
Table DEMO_ADDRESSES created. 100,000 rows inserted. Commit complete. PL/SQL procedure successfully completed. COLUMN_NAME NUM_DISTINCT ______________ _______________ CITY 100 COUNTRY 10
The Misestimate
Example:
select count(*) as addresses from demo_addresses where city = 'City7' and country = 'Country7'; explain plan for select * from demo_addresses where city = 'City7' and country = 'Country7'; select * from dbms_xplan.display(format => 'BASIC +ROWS +PREDICATE');
Output:
ADDRESSES
____________
1000
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________
Plan hash value: 638069309
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |
|* 1 | TABLE ACCESS FULL| DEMO_ADDRESSES | 100 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CITY"='City7' AND "COUNTRY"='Country7')
13 rows selected.The optimizer computes 100,000 × 1/100 cities × 1/10 countries = 100 rows. In reality every address in City7 is in Country7, so the second condition removes nothing, and there are 1,000 rows: ten times the estimate. In a join, such an error is multiplied further and can lead to a nested loop over far more rows than planned.
Create a Column Group
Example:
select dbms_stats.create_extended_stats(user, 'DEMO_ADDRESSES', '(CITY, COUNTRY)')
as extension
from dual;
exec dbms_stats.gather_table_stats(user, 'DEMO_ADDRESSES')
select column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'DEMO_ADDRESSES' and column_name <> 'ADDRESS_ID';Output:
EXTENSION _________________________________ SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z PL/SQL procedure successfully completed. COLUMN_NAME NUM_DISTINCT HISTOGRAM _________________________________ _______________ ____________ CITY 100 FREQUENCY COUNTRY 10 FREQUENCY SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 100 NONE
The column group appears as a hidden virtual column, SYS_STU..., with 100 distinct values: the 100 combinations of city and country that exist. The gathering also created histograms on CITY and COUNTRY, because the query above had used both columns, but not on the column group.
Why the Estimate Did Not Change
Example:
explain plan for select * from demo_addresses where city = 'City7' and country = 'Country7'; select * from dbms_xplan.display(format => 'BASIC +ROWS +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________
Plan hash value: 638069309
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |
|* 1 | TABLE ACCESS FULL| DEMO_ADDRESSES | 100 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CITY"='City7' AND "COUNTRY"='Country7')
13 rows selected.Still 100 rows. When the individual columns have histograms and the column group has none, the optimizer does not use the column group, as this plan shows. The fix is a histogram on the column group as well.
Fix: A Histogram on the Column Group
Example:
-- a histogram on the column group as well
begin
dbms_stats.gather_table_stats(user, 'DEMO_ADDRESSES',
method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS (CITY, COUNTRY) SIZE 254');
end;
/
select column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'DEMO_ADDRESSES' and column_name <> 'ADDRESS_ID';
explain plan for
select * from demo_addresses where city = 'City7' and country = 'Country7';
select * from dbms_xplan.display(format => 'BASIC +ROWS +PREDICATE');Output:
PL/SQL procedure successfully completed.
COLUMN_NAME NUM_DISTINCT HISTOGRAM
_________________________________ _______________ ____________
CITY 100 FREQUENCY
COUNTRY 10 FREQUENCY
SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 100 FREQUENCY
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________
Plan hash value: 638069309
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 |
|* 1 | TABLE ACCESS FULL| DEMO_ADDRESSES | 1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CITY"='City7' AND "COUNTRY"='Country7')
13 rows selected.With a frequency histogram on the column group, the estimate is 1,000 rows, exactly right. Gathering with SIZE 1 for all columns would also make the optimizer use the group, without any histograms.
Combinations That Do Not Exist
Example:
-- a combination that does not exist select count(*) as addresses from demo_addresses where city = 'City7' and country = 'Country3'; explain plan for select * from demo_addresses where city = 'City7' and country = 'Country3'; select * from dbms_xplan.display(format => 'BASIC +ROWS +PREDICATE');
Output:
ADDRESSES
____________
0
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________
Plan hash value: 638069309
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 500 |
|* 1 | TABLE ACCESS FULL| DEMO_ADDRESSES | 500 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CITY"='City7' AND "COUNTRY"='Country3')
13 rows selected.City7 is not in Country3, so no row matches. The histogram has no entry for the pair, and the optimizer assumes half the count of the least frequent combination it knows, 500 rows. Such queries are rare in practice, since applications ask for combinations that exist.
Things to Know
- Column groups help equality conditions on all their columns. They do not help range conditions, or queries that use only some of the columns.
- Extended statistics also exist for expressions, for example (UPPER(email)), so that the optimizer knows the distribution of a function's result.
- DBMS_STATS.DROP_EXTENDED_STATS removes a column group.
Related Guides
Conclusion
For correlated columns, the optimizer's multiplication of selectivities underestimates the rows. Create a column group with DBMS_STATS.CREATE_EXTENDED_STATS, gather statistics, and make sure the group has a histogram when its columns do; then the estimate reflects the combinations that really exist.
