sys_stat_analyze
Collects statistics on a table and its columns for use in SQL optinmization
sys_stat_analyze
(in table_name varchar,
[in pcnt integer],
[in ignore_vdb integer]);
Description
This function collects (or updates) column statistics for the table columns.
It collects minimum, maximum average and distinct values for a column and a
row count for the table and inserts the data into the DB.DBA.SYS_COL_STAT table.
It does not make historgrams for the columns.
The statistics are then used by the Optimized SQL compiler. All the cached compilations are
discarded, because some of them may compile differently in the light of the new data.
This function will normally consider the entire database with the exception
of remote tables. Since you may be concerned about time or remote tables this
operation is configurable using the optional parameters,
pcnt and ignore_vdb.
Parameters
table_name –
The full name of the table exactly as in the KEY_TABLE column of SYS_KEYS.
pcnt –
Determines how much of the database to gather statistics for.
Legal values are 0, or between 5 - 500. Any non-zero value will gather
information on a random subset of the database tables, the greater the value,
the larger the subset.
ignore_vdb –
Determines whether remote tables are considered. By default
remote tables (ignore_vdb = 1) are ignored. Setting this value to 0
will cause this function to gather statistical information for remote
tables also.