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 Mon, 31 Oct 2005 20:10:03 GMT

I did not get any replies to this email.  It would be useful if
someone could clarify whether the statistics are automatically updated
or not.  


>>>>> "ØG" == Øystein Grøvlen <Oystein.Grovlen@Sun.COM> writes:

>>>>> "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).

    ØG> The tuning guide says:

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

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

    ØG> The "When cardinality statistics are automatically updated" section
    ØG> says

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

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

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

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

    ØG> -- 
    ØG> Øystein

View raw message