Collecting statistics in shadow table environments

When you collect catalog statistics on tables in shadow table environments, the query optimizer uses this information to choose the best access plans for queries.

Before you begin

Ensure that you have the required authorization for the RUNSTATS command.

About this task

You must keep table statistics, including shadow table statistics, up-to-date. If the auto_runstats database configuration parameter is not set to ON, you must manually collect statistics on shadow tables after they are initially populated with data and periodically thereafter.

Procedure

To collect statistics on shadow tables:

  1. Connect to the database that contains the tables for which you want to collect statistics.
  2. Issue the RUNSTATS command with the appropriate parameters.
    The following example shows how to collect statistics on the table DTW.TRADE_SHADOW:
    RUNSTATS ON TABLE DTW.TRADE_SHADOW ON ALL COLUMNS
            WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL
  3. When the RUNSTATS command finishes running, release locks by issuing a COMMIT statement.
  4. Rebind any packages that access the tables for which you updated statistics.