Gather hidden


Interestingly, dbms_stats will collect statistics on hidden columns, eg, from a function based index, but it doesn't appear that you can actually retrieve them

SQL> create table T ( x number ) ;

Table created.

SQL> create index TX on T ( x+10 );

Index created.

SQL> select column_name from dba_tab_cols
  2  where table_name = 'T'
  3  and owner = user;

COLUMN_NAME
------------------------------
SYS_NC00002$
X

SQL> declare
  2   srec dbms_stats.statrec;
  3   DISTCNT number;
  4   DENSITY number;
  5   NULLCNT number;
  6   AVGCLEN number;
  7  begin
  8   dbms_stats.GET_COLUMN_STATS
  9   (OWNNAME=>user
 10   ,TABNAME=>'T'
 11   ,COLNAME=>'SYS_NC00002$'
 12   ,DISTCNT=>distcnt
 13   ,DENSITY=>density
 14   ,NULLCNT=>nullcnt
 15   ,SREC=>srec
 16   ,AVGCLEN=>avgclen);
 17  end;
 18  /
declare
*
ERROR at line 1:
ORA-20000: Unable to get values for column SYS_NC00002$
ORA-06512: at "SYS.DBMS_STATS", line 3976
ORA-06512: at "SYS.DBMS_STATS", line 3991
ORA-06512: at line 8


SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select column_name , num_distinct
  2  from dba_tab_cols
  3  where table_name = 'T'
  4  and owner = user;

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
SYS_NC00002$                              0
X                                         0