Using statistical views
A view must be enabled for optimization before its statistics can be used to optimize a query. A view that is enabled for optimization is known as a statistical view.
About this task
A view that is not a statistical view is said to be disabled for optimization and is known as a regular view. A view is disabled for optimization when it is first created. Use the ALTER VIEW statement to enable a view for optimization. For privileges and authorities that are required to perform this task, see the description of the ALTER VIEW statement. For privileges and authorities that are required to use the RUNSTATS utility against a view, see the description of the RUNSTATS command.
- The view directly or indirectly references a materialized query table (MQT). (An MQT or statistical view can reference a statistical view.)
- The view directly or indirectly references a catalog table.
- The view is inoperative.
- The view is a typed view.
- There is another view alteration request in the same ALTER VIEW statement.
- Aggregation or distinct operations
- Union, except, or intersect operations
- OLAP specification
- Enable the view for optimization. A view can be enabled for optimization using the ENABLE OPTIMIZATION clause on the ALTER VIEW statement. A view that has been enabled for optimization can subsequently be disabled for optimization using the DISABLE OPTIMIZATION clause. For example, to enable MYVIEW for optimization, enter the following:
alter view myview enable query optimization
- Invoke the RUNSTATS command.
For example, to collect statistics on MYVIEW, enter the following:
To use row-level sampling of 10 percent of the rows while collecting view statistics, including distribution statistics, enter the following:
runstats on table db2dba.myview
To use page-level sampling of 10 percent of the pages while collecting view statistics, including distribution statistics, enter the following:
runstats on table db2dba.myview with distribution tablesample bernoulli (10)
runstats on table db2dba.myview with distribution tablesample system (10)
- Optional: If queries that are impacted by the view definition are part of static SQL packages, rebind those packages to take advantage of changes to access plans resulting from the new statistics.