Statistics with volatile table

[Otto Carl Marte]

>From the IBM db2 docs:
http://publib.boulder.ibm.com/infoce...n/t0005308.htm

it says that volatile tables (ALTER TABLE mytable VOLATILE
CARDINALITY) do not use statistics. I would just like to clarify this
statement. Does this mean that no statistics at all are used when
determining the execution plan? or does this mean that statistics
applying to cardinality are not used when calculating the execution
plan?

If this means that no statistics at all are used when the optimizer
decided the execution plan is it possible for the optimizer to choose
an incorrect index for a query? i.e How safe is setting a table to
have volatile cardinality?

[janman]

On Jun 28, 11:35 am, Otto Carl Marte <[e-mail]:

From the IBM db2 docs:

http://publib.boulder.ibm.com/infoce...x.jsp?topic=/c...

it says that volatile tables (ALTER TABLE mytable VOLATILE
CARDINALITY) do not use statistics. I would just like to clarify this
statement. Does this mean that no statistics at all are used when
determining the execution plan? or does this mean that statistics
applying to cardinality are not used when calculating the execution
plan?

If this means that no statistics at all are used when the optimizer
decided the execution plan is it possible for the optimizer to choose
an incorrect index for a query? i.e How safe is setting a table to
have volatile cardinality?

No statistics are used. The optimizer uses index scan Only if all
referenced columns are in the index else it's table scan.
We use volatile on a couple of tables due to extreme card
changability. The case we had was at the time of runstats the card was
0 and so the access is table scan but the cards increased to a million
and it still used table scan. Changing to volatile worked fine as it
did index scan.

[Otto Carl Marte]

Thanks, that is exactly the case (empty table that increases to a
large table) we want to use volatile tables for. The concern we have
is that for some tables with multiple indices the incorrect index will
be used. I suppose what you are saying is that we should be careful to
ensure we have correct indices for our queries when using a volatile
table. But then again, you always have to be careful to have the
correct indices for large tables :-)

[Serge Rielau]

Comments from backstage:
Volatile tables should be used if the tables are truly volatile when the
tables grow and shrink dramatically and unpredictably so that RUNSTATS
at any given time could be misleading. If there are some statistics on
the table, they may be used in conjunction with some on-the-fly
statistics fabrication that is not easy to describe.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab