what is last_analysed date for oracle index means?

Question

what does the last_analyzed date of oracle index means? I am looking at an index whose last_analyzed date is 2011-10-03 22:09:16. Does this mean the indexing is not working?

Answer 1

It is the date that statistics were last gathered for that index. Depending on the amount of change going on in the table that the index is on, this might mean your index is not being considered by the optimizer, given the length of time it has been since stats were gathered for this object. If you find that the index is not being used when you think it should be, then you can manually run the statistics gathering job for the index by using the DBMS_STATS package:

exec dbms_stats.gather_index_stats('OWNER_NAME','INDEX_NAME');

gather_index_stats is described here.

You will need DBA or GATHER_SYSTEM_STATISTICS roles.

Out of the box, Oracle installations typically have a scheduled task that gathers statistics periodically (SYS.GATHER_STATS_JOB). However, the defaults for when the job chooses to re-analyze an object are sometimes inadequate. Here is a pretty good white paper on statistics from Oracle.