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.
--
Øystein
>>>>> "ØG" == Øystein Grøvlen <[EMAIL PROTECTED]> writes:
>>>>> "MM" == Mike Matrigali <[EMAIL PROTECTED]> 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
