Statistics Schema Collection

Statistics schema collection returns statistics that have been collected on TABLE, JOIN INDEX or HASH INDEX objects by accessing the Advanced SQL Engine Data Dictionary views. It will access the DBC.StatsV or DBC.StatsVX Data Dictionary view based on the UseXViews Connection setting.

No DBC.StatsVX view currently exists. We will therefore attemp to use the DBC.StatsV view in these cases even if UseXViews is true.

Column Name	Data Type	Description
TABLE_CATALOG	String	This field is always set to EMPTY.
TABLE_SCHEMA	String	Returns the name of the DATABASE in which the TABLE, JOIN INDEX or HASH INDEX object resides.
TABLE_NAME	String	Returns the name of the TABLE, JOIN INDEX or HASH INDEX on which statistics were collected.
COLUMN_NAME	String	Returns the name of the COLUMN or expression on which statistics were collected. This may be a comma separated list of column names.
ALIAS_NAME	String	Returns the name that was assigned when the statistics were collected, or null.
STATISTICS_TYPE	String	Returns: Table to designate a TABLE object. Join Index to designate a JOIN INDEX object. Hash Index to designate a HASH INDEX object. Base Temp Table to designate a Global Temporary Table base definition. Materialized Temp Table to designate a Global Temporary Table instance.
SOURCE	String	Returns: Internal to designate that the row was generated internally. User Collected to designate the the statistics were created by a user executing a COLLECT STASTISTICS statement. User Collected Values to designate the the statistics were created by a user executing a COLLECT STASTISTICS statement. Copied to designate that the row was copied. Transferred to designate that the row was created as the result of a CREATE TABLE AS statement.
IS_VALID	String	Returns Y if the statistics are valid or N otherwise.
SAMPLE_TYPE	String	Returns the options specified for a Sample collection.
SAMPLE_PERCENT	Decimal	Returns the sample percentage specified for collection, or null.
THRESHOLD	String	Returns the options specified for Threshold collection.
MAX_INTERVALS	Int16	Returns the maximum number of intervals specified by the user, or null.
MAX_LENGTH	Int32	Returns the maximum value length specified by the user, or null.
ROW_COUNT	Double	Returns the total number of rows in the table or Index.
UNIQUE_COUNT	Double	Returns the number of unique values for this expression.
NULL_UNIQUE_COUNT	Double	Returns the number of unique values where some columns in the expression are null.
NULL_COUNT	Double	Returns the number of rows where some columns in this expression are null.
ALL_NULL_COUNT	Double	Returns the number of rows where all columns in this expression are null.
HIGH_MODE_FREQUENCY	Double	Returns the number of rows containing the most common value.
NULL_HIGH_MODE_FREQUENCY	Double	Returns the number of rows containing the most common value where some columns in the expression are null.
SKIP_COUNT	Int32	Returns the number of times statistics collection has been skipped for this expression.
BLC_RATIO	Int32	Returns the estimated percentage of space saved by manual BLC compression.
DATE_CREATED	DateTime	Returns the date and time the statistics were first created.
DATE_MODIFIED	DateTime	Returns the date and time the statistics were last refreshed.

Statistics schema collection can be filtered or restricted to:

Restriction Column Name	Restriction Number	Description
TABLE_SCHEMA	1	This restriction can be set to a valid DATABASE object name.
TABLE_NAME	2	This restriction can be set to a valid TABLE, JOIN INDEX or HASH INDEX object name.
STATISTICS_TYPE	3	This restriction can be set to: T for TABLE Objects I for JOIN INDEX Objects N for HASH INDEX Objects B for GLOBAL TEMP TABLE base definitions M for GLOBAL TEMP TABLE instances

Restriction Column Name

Restriction Number

Description

TABLE_SCHEMA

This restriction can be set to a valid DATABASE object name.

TABLE_NAME

This restriction can be set to a valid TABLE, JOIN INDEX or HASH INDEX object name.

STATISTICS_TYPE

This restriction can be set to:

T for TABLE Objects
I for JOIN INDEX Objects
N for HASH INDEX Objects
B for GLOBAL TEMP TABLE base definitions
M for GLOBAL TEMP TABLE instances

Statistics schema collection rows are ordered by:

Column Name	Description
TABLE_SCHEMA	Ascending case sensitive sort order. For example A through Z followed by a through z.
TABLE_NAME	Ascending case sensitive sort order.
COLUMN_NAME	Ascending case sensitive sort order.