How do you know whether the index is analyzed or not in a table ...?

Many of times we create indexes and forget to analyze them. Here is a simple way to find out whether your indexes or tables have been analyzed alteast once or not ...

- To find if a table is analyzed, check the "AVG_ROW_LEN" column of the USER_TABLES table. If it is non-zero, the 'analyze' or 'dbms_stats' command has been run at least once on this table.

- To find if an index is analyzed, check the "COLUMN_LENGTH " column of the USER_IND_COLUMNS table. If it is non-zero, the 'analyze' or 'dbms_stats' command has been run at least once on this index.

But why do we need to analyze the indexes or tables ??
Because oracle uses the cost based optimizer to give the best performance in most of the cases. And the cost based optimizer needs data to decide the access plan, and this data is generated by the analyze command or the newer dbms_stats package.

So always analyze or dbms_stats the table and indexes.
If you analyze a table, then its current indexes are automatically analyzed too. If you analyze a index, then the table is not analyzed.