Name
sys_stat_analyze — Collects statistics on a table and its columns for use in SQL optimization
Synopsis
sys_stat_analyze
(
|
in table_name varchar , |
in pcnt integer , | |
in
ignore_vdb
integer
) ; |
Description
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 histograms 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 the sample size for statistics gathering. A value of 0 will read the whole table. The default is 5, for an approximately five percentsample of the table.
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. A percentage of 0 will read the remote table from beginning to end. A non-zero percentage will access the statistics tables of the remote DBMS if it is of a supported type, see the sys_stat_vdb_mappers table.