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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to DBMS_STATS queries invisible indexes

  1. Pingback: Log Buffer #297, A Carnival of the Vanities for DBAs | The Pythian Blog

Leave a Reply