I have read as much as I can about this subject and am a little confused. I understand the cardinality statistics are not computed if the tables are empty when indexes are created and I can work around that.
Say I have a table with 15 million records in it and there is a main index that I need to be used to query and I update the statistics and verify that query optimizer is using that index. This table will have many rows inserted and many rows deleted every day. Basically the index is a function of a timestamp and a counter. So older records are going to be deleted and newer records are going to be inserted.
My question is will the query optimizer revert back to doing a table scan at some point instead of using the index. Basically will the index go stale?
A second question is does a call to “syscs_util.syscs_update_statistics’ lock a table/index while it is being performed? This tables of concern where the indexes might go stale never have a free second in which inserts and deletes are being done….
Thanks for any information.