DBMS_STATS queries invisible indexes

I thought that an invisible index was never queried, but that it was only maintained as the table was modified.  But, when you use the dbms_stats package to gather statistics on a table, including its indexes, the invisible indexes are queried and their statistics are updated.

Here is part of the output from my  test script:

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),
     VISIBILITY
  2  from user_indexes where index_name='TESTI';

TO_CHAR(LAST_ANALYZ VISIBILIT
------------------- ---------
2012-11-30 16:56:36 INVISIBLE

SQL> 
SQL> execute dbms_stats.gather_table_stats(NULL,'TEST');

PL/SQL procedure successfully completed.

SQL> 
SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),
     VISIBILITY
  2  from user_indexes where index_name='TESTI';

TO_CHAR(LAST_ANALYZ VISIBILIT
------------------- ---------
2012-11-30 16:56:38 INVISIBLE

Notice that the last_analyzed seconds change when I gather stats on my test table.  It’s a bummer because you may not want to spend the resources updating the stats on large indexes that you never use such as primary key invisible indexes.  In our case this is on Exadata where we want to use smart scans and not use the index, but we want the primary key index to force uniqueness.

– Bobby