db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <Br...@canoga.com>
Subject Question on cardinality, statistics, and when things go stale
Date Thu, 18 Nov 2010 16:07:56 GMT
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.


View raw message