DB2 10.5 for Linux, UNIX, and Windows

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.

A view cannot be enabled for optimization if any one of the following conditions is true:

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.

If the definition of a view that is being altered to enable optimization contains any of the following items, a warning is returned, and the optimizer will not exploit the view's statistics:

Aggregation or distinct operations
Union, except, or intersect operations
OLAP specification

Procedure

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:
```
   runstats on table db2dba.myview
```
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 with distribution tablesample bernoulli (10)
```
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 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.