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

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.

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

• Choosing column compression

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;