Categories:
Miscellaneous Functions (System)

SYSTEM$CLUSTERING_RATIO

Calculates the clustering ratio for a table, based on one or more columns in the table. The ratio is a number from 0 to 100. The higher the ratio, the better clustered the table is.

The clustering ratio for a table can be calculated using any columns in the table or columns that have been explicitly defined as clustering keys for the table. Clustering keys can be defined for a table using either CREATE TABLE or ALTER TABLE.

For more information about clustering ratio and clustering keys, see Understanding Snowflake Table Structures.

See also:
SYSTEM$CLUSTERING_INFORMATION , SYSTEM$CLUSTERING_DEPTH

Syntax

SYSTEM$CLUSTERING_RATIO( '<table_name>' , '( <col1> [, <col2> ... ] )' [, '<predicate>' ] )

Arguments

table_name
Table for which you want to calculate the clustering ratio.
col1 [ , col2 ... ]

Column(s) in the table used to calculate the clustering ratio:

  • For tables with no clustering keys, this argument is required. If this argument is omitted, an error is returned.
  • For tables with clustering keys, this argument is optional; if the argument is omitted, Snowflake uses the defined clustering keys to calculate the ratio.

Note

You can use this argument to calculate the ratio for any columns in the table, regardless of the clustering keys defined for the table.

predicate
Clause that filters the range of values in the columns on which to calculate the clustering ratio. Note that predicate does not utilize a WHERE keyword at the beginning of the clause.

Usage Notes

  • All arguments are strings, i.e. they must be enclosed in single quotes.

  • If predicate contains a string, the string must be enclosed in single quotes, which then must be escaped using single quotes, e.g.:

    SYSTEM$CLUSTERING_RATIO( ... , 'col1 = 100 and col2 = ''A''' )

Examples

Calculate the clustering ratio for a table using two columns in the table:

SELECT SYSTEM$CLUSTERING_RATIO('t2', '(col1, col3)');

+-------------------------------+
| SYSTEM$CLUSTERING_RATIO('T2') |
|-------------------------------|
|                          77.1 |
+-------------------------------+

Calculate the clustering ratio for a table using two columns in the table and a predicate on one of the columns:

SELECT SYSTEM$CLUSTERING_RATIO('t2', '(col1, col2)', 'col1 = ''A''');

+-------------------------------+
| SYSTEM$CLUSTERING_RATIO('T2') |
|-------------------------------|
|                          87.7 |
+-------------------------------+

Calculate the clustering ratio for a table using the clustering keys defined for the table:

SELECT SYSTEM$CLUSTERING_RATIO('t1');

+-------------------------------+
| SYSTEM$CLUSTERING_RATIO('T1') |
|-------------------------------|
|                         100.0 |
+-------------------------------+