0

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?

Improve this question
3
  • According documentation: Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer. I think Cost-based optimizer became standard in Oracle 9, i.e. 20 years ago. Unless you use ancient Rule-based optimizer there is not reason to use ANALYZE command at all.
    – Wernfried Domscheit
    Commented Jan 9, 2020 at 9:36
  • As previously stated, I'm not ment to gather objects statistics. I'm analyzing the index structure in order to make the Oracle database to estimate the best basic compression to be eventually applied to the index.
    – Max
    Commented Jan 9, 2020 at 9:53
  • @WernfriedDomscheit ANALYZE is still a valid option for gathering statistics which are not gathered by DBMS_STATS.
    – Balazs Papp
    Commented Jan 9, 2020 at 9:54

1 Answer 1

Reset to default
1

The contents of INDEX_STATS are 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.

Improve this answer
1
  • I didn't notice such behaviour. Thanks for having pointed that out!
    – Max
    Commented Jan 9, 2020 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.