SVV_ALTER_TABLE_RECOMMENDATIONS - Amazon Redshift
Table columnsSample queries
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SVV_ALTER_TABLE_RECOMMENDATIONS

Records the current Amazon Redshift Advisor recommendations for tables. This view shows recommendations for all tables, whether they are defined for automatic optimization or not. To view if a table is defined for automatic optimization, see SVV_TABLE_INFO. Entries appear only for tables visible in the current session's database. After a recommendation has been applied (either by Amazon Redshift or by you), it no longer appears in the view.

SVV_ALTER_TABLE_RECOMMENDATIONS is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
type character(30) The type of recommendation. Possible values are distkey and sortkey.
database character(128) The database name.
table_id integer The table identifier.
group_id integer The group number of a set of recommendations. All recommendations in a group should be applied to see the maximum benefit. Possible values are -1 for a sort key recommendation, and a number larger than zero for a distribution key recommendation.
ddl character(1024) The SQL statement that must run to apply the recommendation.
auto_eligible character(1) The value indicates if the recommendation is eligible for Amazon Redshift to run automatically. If this value is t, then the indication is true, if f then false.

Sample queries

In the following example, the rows in the result show recommendations for distribution key and sort key. The rows also show whether the recommendations are eligible for Amazon Redshift to automatically apply them. 

select type, database, table_id, group_id, ddl, auto_eligible 
from svv_alter_table_recommendations;

 type      | database | table_id | group_id | ddl                                                                                                                                                 | auto_eligible
 diststyle | db0      | 117884   | 2        | ALTER TABLE "sch"."dp21235_tbl_1" ALTER DISTSTYLE KEY DISTKEY "c0"                                                                                  | f
 diststyle | db0      | 117892   | 2        | ALTER TABLE "sch"."dp21235_tbl_1" ALTER DISTSTYLE KEY DISTKEY "c0"                                                                                  | f
 diststyle | db0      | 117885   | 1        | ALTER TABLE "sch"."catalog_returns" ALTER DISTSTYLE KEY DISTKEY "cr_sold_date_sk", ALTER COMPOUND SORTKEY ("cr_sold_date_sk","cr_returned_time_sk") | t
 sortkey   | db0      | 117890   | -1       | ALTER TABLE "sch"."customer_addresses" ALTER COMPOUND SORTKEY ("ca_address_sk")                                                                     | t