db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein.Grov...@Sun.COM (Øystein Grøvlen)
Subject Re: How to update cardinality statistics
Date Thu, 20 Oct 2005 12:25:09 GMT
>>>>> "MM" == Mike Matrigali <mikem_app@sbcglobal.net> writes:

    MM> Would need to see the exact documentation reference, but this is not
    MM> the case.  The storage system keeps an estimate of the number of
    MM> rows in a table.  For performance reasons this estimate is not exact
    MM> (ie.  we don't  update a  single could  for  every insert/delete/abort
    MM> insert/abort delete/commit).  The actual update is tied to the time

    MM> pages move out of the cache.  Because the number can drift there is
    MM> an optimization in the language execution engine that if it has just
    MM> executed a complete scan of the table then it updates the value
    MM> in the storage system.

    MM> But in almost all cases the automatic maintained row count maintained
    MM> by store is good enough and never needs to be updated.  The scan
    MM> optimization is there to automatically catch the drift if it is
    MM> cheap to do so, it wasn't really meant for users to use to explicitly
    MM> update it (it was take advantage if a user happened to be executing
    MM> a complete scan for some other reason).

The tuning guide says:

----------------------------------------------------------------------
As you saw in When cardinality statistics are automatically updated,
cardinality statistics are automatically updated only in limited
cases. Normal insert, update, and delete statements do not cause the
statistics to be updated. This means that statistics can go
stale. Stale statistics can slow your system down, because they worsen
the accuracy of the optimizer's estimates of selectivity.
----------------------------------------------------------------------

Note especially: "Normal insert, update, and delete statements do not
cause the statistics to be updated."  Is this wrong?  

The "When cardinality statistics are automatically updated" section
says

----------------------------------------------------------------------
For the following operations that you perform on a table, Derby
automatically creates statistics. Those operations are:

    * (new index only) When you create a new index on an existing
      non-empty table.
    * (new backing indexes only) When you add a primary key, unique,
      or foreign key constraint to an existing non-empty table.

For other operations, Derby automatically updates statistics for the
table and all indexes on the table if they are already exist. Those
operations are:

    * (all indexes) When you execute SYSCS_UTIL.SYSCS_COMPRESS_TABLE.
    * (index only) When you drop a column that is part of a table's
      index; the statistics for the affected index are dropped, and
      statistics for the other indexes on the table are updated.
----------------------------------------------------------------------

-- 
Øystein


Mime
View raw message