Table statistics for staging / interface tables

Let's say you have an interface table which is usually empty. Let's say you also have a dilligent DBA who runs Gather Schema Stats periodically. Chances are the stats for this table will indicate that it is empty. This can lead to atrocious performance of queries involving this table when you actually fill it with data and run the associated interface program.

In the past I'd resolve this by altering the interface program to explicitly gather stats for the table before executing. However, in 10g at least, there is a much more pleasant way to deal with this - at least for situations where there are only a few SQL operations over the data in the table (if there are lots then it might still be worth gathering stats once at the start of the operation).

If Oracle encounters a table with missing statistics it will usually automatically do dynamic sampling of the table as part of determining the execution plan (unless dynamic sampling is turned off) - which is great!

So all we want to do is:

• Delete the statistics for the interface table

DBMS_STATS.DELETE_TABLE_STATS (
  ownname          VARCHAR2,
  tabname          VARCHAR2,
  partname         VARCHAR2 DEFAULT NULL,
  stattab          VARCHAR2 DEFAULT NULL,
  statid           VARCHAR2 DEFAULT NULL,
  cascade_parts    BOOLEAN  DEFAULT TRUE,
  cascade_columns  BOOLEAN  DEFAULT TRUE,
  cascade_indexes  BOOLEAN  DEFAULT TRUE,
  statown          VARCHAR2 DEFAULT NULL,
  no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                    get_param('NO_INVALIDATE')),
  force            BOOLEAN DEFAULT FALSE);

• Lock the stats so that none are gathered the next time Gather Schema Stats is run

DBMS_STATS.LOCK_TABLE_STATS (
  ownname    VARCHAR2,
  tabname    VARCHAR2);

Now, every time our interface runs, Oracle will dynamically sample whatever data is in the interface table - which ought to result in a much better plan than one based on the assumption of an empty table.

Best of all no code change was required.