Auto‑Detect Useful Column Groups in Oracle 12c to Improve Query Plans

Environment Preparation

First, verify the Oracle version and create a test table customers_test as a copy of the sample SH.customers table.

SQL> select banner from v$version;

SQL> conn sh/sh@HOEGH

SQL> DROP TABLE customers_test;

SQL> CREATE TABLE customers_test AS SELECT * FROM customers;

SQL> select count(*) from customers_test;

The table contains 55,500 rows.

Gather Initial Statistics

Collect basic table statistics.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'customers_test');

Enable Workload Monitoring

Open a separate session as SYS and start column‑usage monitoring for 300 seconds (5 minutes).

SQL> show user

SQL> BEGIN DBMS_STATS.SEED_COL_USAGE(null,null,300); END;

Explain Initial Execution Plan

Run an EXPLAIN PLAN for a query that filters on cust_city, cust_state_province and country_id.

SQL> EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city='Los Angeles' AND cust_state_province='CA' AND country_id=52790;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic rows'));

The plan shows a full table scan with an estimated row count of 1.

View Column Usage Information

After the monitoring period, retrieve the column‑usage report.

SQL> SET LONG 100000

SQL> SET LINES 120

SQL> SET PAGES 0

SQL> SELECT DBMS_STATS.REPORT_COL_USAGE(user,'customers_test') FROM DUAL;

The report indicates that the columns cust_city and cust_state_province are frequently used together with country_id, forming a useful column group.

Create Extended Statistics

Generate an extended statistics object for the identified column group.

SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user,'customers_test') FROM DUAL;

The output shows the creation of an object (e.g., SYS_STUMZ$C3AIHLPBROI#SKA58H_N) for the group (cust_city, cust_state_province, country_id).

Re‑Gather Table Statistics

Collect statistics again so the new extended stats are taken into account.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'customers_test');

Verify Column Statistics

Query USER_TAB_COL_STATISTICS to confirm the extended statistics appear.

SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='CUSTOMERS_TEST' ORDER BY 1;

The result includes the generated column‑group name with a HYBRID histogram, confirming the extended stats are stored.

Explain Plan After Extended Stats

Run the same EXPLAIN PLAN query again.

SQL> EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city='Los Angeles' AND cust_state_province='CA' AND country_id=52790;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic rows'));

The new plan now estimates 867 rows instead of 1, reflecting the optimizer’s use of the extended statistics and providing a more realistic cardinality.

Conclusion

Oracle 12c can automatically detect useful column groups via DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE. Creating extended statistics for these groups significantly improves the optimizer’s row‑count estimates, leading to better execution plans for queries that filter on the grouped columns.