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.

 

Brett