sa_column_stats system procedure

Returns various statistics about the specified column(s). These statistics are not related to the column statistics maintained for use by the optimizer.

Syntax

sa_column_stats ( 
[ tab_name ]
[, col_name ]
[, tab_owner ]
[, max_rows ]
)

Arguments

tab_name This optional CHAR(128) parameter specifies the name of the table. If this parameter is not specified, statistics are calculated for all columns in all table(s).
col_name This optional CHAR(128) parameter specifies the columns for which to calculate statistics. If this parameter is not specified, statistics are calculated for all columns in the specified table(s).
tab_owner This optional CHAR(128) parameter specifies the owner of the table. If this parameter is not specified, the database server uses the owner of the first table that matches the tab_name specified.
max_rows This optional INTEGER parameter specifies the number of rows to use for the calculations. If this parameter is not specified, 1000 rows are used by default. Specifying 0 instructs the database server to calculate the ratio based on all the rows in the table.

Result set

With the exception of table_owner, table_name, and column_name, all values in the result set are NULL for non-string columns. Also, for empty tables, num_rows_processed and num_values_compressed are 0, while all other values are NULL.

Column name	Data type	Description
table_owner	CHAR(128)	The owner of the table.
table_name	CHAR(128)	The table name.
column_name	CHAR(128)	The column name.
num_rows_processed	INTEGER	The total number of rows read to calculate the statistics.
num_values_compressed	INTEGER	The number of values in the column that are compressed. If the column is not compressed, the value is 0.
avg_compression_ratio	DOUBLE	The average compression ratio, expressed as a percentage reduction in size, for compressed values in the column. If the column is not compressed, the value is NULL.
avg_length	DOUBLE	The average length of all non-NULL strings in the column.
stddev_length	DOUBLE	The standard deviation of the lengths of all non-NULL strings in the column.
min_length	INTEGER	The minimum length of non-NULL strings in the column.
max_length	INTEGER	The maximum length of strings in the column.
avg_uncompressed_length	DOUBLE	The average length of all uncompressed, non-NULL strings in the column.
stddev_uncompressed_length	DOUBLE	The standard deviation of the lengths of all uncompressed, non-NULL strings in the column.
min_uncompressed_length	INTEGER	The minimum length of all uncompressed, non-NULL strings in the column.
max_uncompressed_length	INTEGER	The maximum length of all uncompressed, non-NULL strings in the column.

Remarks

The database server determines the columns that match the owner, table, and column names specified, and then for each one, calculates statistics for the data in each specified column. By default, the database server only uses the first 1000 rows of data.

For avg_compression_ratio, values cannot be greater than, or equal to 100, however, they can be less than 0 if highly incompressible data (for example, data that is already compressed) is inserted into a compressed column. Higher values indicate better compression. For example, if the number returned is 80, then the size of the compressed data is 80% less than the size of the uncompressed data.

Permissions

DBA authority required

Side effects

None

Example

In this example, you use the sa_column_stats system procedure in a SELECT statement to determine which columns in the database are benefitting most from column compression:

SELECT * FROM sa_column_stats() 
  WHERE num_values_compressed > 0 
  ORDER BY avg_compression_ratio desc;

In this example, you narrow your selection from the previous example to tables owned by bsmith:

SELECT * FROM sa_column_stats( tab_owner='bsmith' ) 
  WHERE num_values_compressed > 0 
  ORDER BY avg_compression_ratio desc;

Discuss this page in DocCommentXchange.
Send feedback about this page using email.