Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_clean_database system procedure Next Page

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
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 nameData typeDescription
table_ownerCHAR(128)The owner of the table.
table_nameCHAR(128)The table name.
column_nameCHAR(128)The column name.
num_rows_processedINTEGERThe total number of rows read to calculate the statistics.
num_values_compressedINTEGERThe number of values in the column that are compressed. If the column is not compressed, the value is 0.
avg_compression_ratioDOUBLEThe 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_lengthDOUBLEThe average length of all non-NULL strings in the column.
stddev_lengthDOUBLEThe standard deviation of the lengths of all non-NULL strings in the column.
min_lengthINTEGERThe minimum length of non-NULL strings in the column.
max_lengthINTEGERThe maximum length of strings in the column.
avg_uncompressed_lengthDOUBLEThe average length of all uncompressed, non-NULL strings in the column.
stddev_uncompressed_lengthDOUBLEThe standard deviation of the lengths of all uncompressed, non-NULL strings in the column.
min_uncompressed_lengthINTEGERThe minimum length of all uncompressed, non-NULL strings in the column.
max_uncompressed_lengthINTEGERThe 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 uncompressible 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

See also
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;