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.
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
DBA_INDEXES for saving the content of
INDEX_STATS to a final table containing the data from all the schemas?