My purpose is to analyze the indexes of a schema for getting the best basic index compression to apply.
I'm going to use the command
ANALYZE INDEX OWNER.INDEX_NAME VALIDATE STRUCTURE;
That will fill up SYS.INDEX_STATS view with the info I need, but considering this view does not have the OWNER column, this is not helping me. Here's why:
In my Oracle instance I have many schemas containing the installation of the same product filled with data from different customers.
I'm filling INDEX_STATS by looping DBA_INDEXES and calling ANALYZE INDEX, so any call to ANALYZE INDEX OWNER.INDEX_NAME VALIDATE STRUCTURE will overwrite the entry from the previous call on the same index on a different schema.
I may save the content of INDEX_STATS after having analyzed each schema, but for not all the customers have the same tables installed because of modularization of the software, some entries of INDEX_STATS may refer to previous calls to a different schema.
Is there a way for purging the content of INDEX_STATS so I can have a clean situation before analyzing each schema? Or the only solution is to join INDEX_STATS with DBA_INDEXES for saving the content of INDEX_STATS to a final table containing the data from all the schemas?
Best Answer
The contents of
INDEX_STATSare temporary. Every time you start a new session, the view becomes empty. Additionally, this view contains data only for the last index that was analyzed.So there is no need to purge the contents, it is just a matter of organizing your code properly and storing the results in a permanent table if you need them.