Gather Statistics Enhancements in 12c -- 5

Oracle tracks the usage of columns as query predicates to determine candidate columns for the creation of histograms.  In earlier versions, we would query SYS.COL_USAGE$ to identify such columns.

Now, 12c has a report.

UPDATE : It seems that the DBMS_STATS.REPORT_COL_USAGE function has been available in some 11.2.0.x patchset release. Although it doesn't appear in the 11.2.0.3 documentation set that I had downloaded it is now visible in the online documentation set updated to 11.2.0.4

First, I run some candidate queries :

SQL> show user
USER is "HEMANT"
SQL> select owner, count(*) from obj_list_2
  2  where owner like 'SYS%'
  3  group by owner;

OWNER     COUNT(*)
---------------- ----------
SYS        41818
SYSTEM   635

SQL> select owner, count(*) from obj_list_2
  2  where owner like 'HEM%'
  3  group by owner;

OWNER     COUNT(*)
---------------- ----------
HEMANT     1

SQL> 

Then, I check for COL_USAGE :

SQL> variable mycolusagerept clob;
SQL> set long 10000000
SQL> begin
  2  :mycolusagerept := dbms_stats.report_col_usage(
  3  ownname=>'HEMANT',
  4  tabname=>'OBJ_LIST_2');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> print mycolusagerept;

MYCOLUSAGEREPT
--------------------------------------------------------------------------------
LEGEND:
.......

EQ    : Used in single table EQuality predicate
RANGE    : Used in single table RANGE predicate
LIKE    : Used in single table LIKE predicate
NULL    : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER    : Used in single table FILTER predicate
JOIN    : Used in JOIN predicate

MYCOLUSAGEREPT
--------------------------------------------------------------------------------
GROUP_BY   : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR HEMANT.OBJ_LIST_2
.........................................

1. OWNER          : LIKE
###############################################################################


MYCOLUSAGEREPT
--------------------------------------------------------------------------------


SQL> 

The report indicates that the OWNER column has been used as a LIKE predicate.

.
.
.