Multidimensional data layout sorting (preview)
The following is prerelease documentation for the multidimensional data layout sorting of tables,
which is in preview release. The documentation and the feature are both subject to change.
We recommend that you use this feature only with test clusters, and not in production environments.
For preview terms and conditions, see Beta Service Participation in Amazon Service Terms |
Note
This feature is only available using a preview cluster or preview workgroup. To create a preview cluster, see Creating a preview cluster in the Amazon Redshift Management Guide. To create a preview workgroup, see Creating a preview workgroup in the Amazon Redshift Management Guide.
A multidimensional data layout sort key is a type of AUTO sort key that is based
on repetitive predicates found in a workload. If your workload has repetitive
predicates, then Amazon Redshift can improve table scan performance by colocating data rows
that satisfy the repetitive predicates. Instead of storing data of a table in strict
column order, a multidimensional data layout sort key stores data by analyzing
repetitive predicates that appear in a workload. More than one repetitive predicate
can be found in a workload. Depending on your workload, this kind of sort key can
improve performance of many predicates. Amazon Redshift automatically determines if this sort
key method should be used for tables that are defined with an AUTO
sort
key.
For example, suppose you have a table that has data sorted in column order. Many data blocks might need to be examined to determine if they satisfy the predicates in your workload. But, if the data is stored on disk in a predicate order, then fewer blocks need to be scanned to satisfy the query. Using a multidimensional data layout sort key is beneficial in this case.
To view whether a query is using a multidimensional data layout key, see
the step_attribute
column of the SYS_QUERY_DETAIL view.
When the value is multi-dimensional
then multidimensional data layout was used for the query.
To view whether a table defined with the AUTO sort key is using a multidimensional data layout, see
the sortkey1
column of the SVV_TABLE_INFO view.
When the value is padb_internal_mddl_key_col
then multidimensional data layout was used for the table sort key.
To prevent Amazon Redshift from using a multidimensional data layout sort key, choose a different table sort key option other than SORTKEY AUTO
.
For more information on SORTKEY options, see
CREATE TABLE.