Returns various statistics about the specified column(s). These statistics are not related to the column statistics maintained for use by the optimizer.
sa_column_stats ( [ tab_name ] [, col_name ] [, tab_owner ] [, max_rows ] )
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.
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. |
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.
DBA authority
None
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.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |