SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
SQL> analyze index pk_test estimate statistics;
SQL> select to_char(last_analyzed, 'dd.mm.yyyy hh24:mi:ss') when
2 from user_indexes where index_name = 'PK_TEST';
Awesome. Thank you! That looks like it will work very well for what I need.
As a followup, is there an industry standard or rule of thumb as to how often dba's should rebuild their indexes? Understandably, this would vary based upon the type of application system the DB is supporting.
Well its debatable if you need to rebuild indexes regularly. If you goto Jonathan Lewis's or Tom Kyte's website you'll see they recomend NOT rebuilding indexes except when you can prove that there are tangible benefits which outweigh the cost of the rebuild (and that the index wont rapidly return to the state prior to the rebuild).
Having said that due to the way our main application works we find an index rebuild reduces index size by 50%. This is due to a peculiarity with the way the app works which means when customers enter data and then subsequently updates the index multiple times the PK index fragments. Since the table is partitioned by month, once that month is completed there are no longer any updates thus the benifits of the rebuild stay.
The query given is when the index was last analyzed, which does NOT rebuild the index. It simply scans it to update statistics. If you want the date that it was recreated, then use the following. For my example, I am using an index named STARMATX1.
1 select to_char(created,'MM/DD/YYYY HH24:MI.SS')
2 FROM USER_OBJECTS
3 WHERE OBJECT_NAME='STARMATX1';
Thank you. This was eye opening. I was under the impression that Oracle's indexes had to be manually analalyzed periodically because Oracle didn't do it itself and thus it was the dba's job.
I'm still very new to Oracle, but I have had several scripts that crawled until I manually reindexed the my indexes. Maybe, reindex is the wrong word and I should use analyze instead. Is this typical that indexes have to be analyzed manually (or via a scheduled task)?
Thank you. I'll check out the two sites you mentioned. I had heard of Jonathan Lewis before.
You should analyze especially when your data volumes or distribution changes. Be careful with dbms_stats though as the optimizer is not perfect and can get things wrong, and dont forget that dbms_stats has a lot of options, its important to use the parameters correctly.
However you should not rebuild indexes (using alter index rebuild) unless you can get a real benefit as mentioned earlier.