Redshift Observatory

System Table Tracker

System view pg_catalog.svv_alter_table_recommendations version 1.0.76142 / 2024-10-08

View Text

SELECT tbl_recos_str.type,
       tbl_recos_str.database,
       tbl_recos_str.table_id,
       tbl_recos_str.group_id,
       (((((((CAST('ALTER TABLE "' AS text) || tbl_recos_str.schema) || CAST('"."' AS text)) || tbl_recos_str.table_name) || CAST('"' AS text)) || tbl_recos_str.dkr) || tbl_recos_str.comma_dkr_skr) || tbl_recos_str.skr) || tbl_recos_str.enc AS ddl,
       tbl_recos_str.auto_eligible
FROM (SELECT rj.type,
             rj.database,
             rj.schema,
             rj.table_name,
             rj.table_id,
             rj.group_id,
             CASE
               WHEN len(rj.distkey) = 0
                 THEN CAST('' AS text)
               ELSE (CAST(' ALTER DISTSTYLE KEY DISTKEY "' AS text) || rj.distkey) || CAST('"' AS text)
             END AS dkr,
             CASE
               WHEN len(rj.sortkey) = 0
                AND rj.type <> CAST('sortkey' AS text)
                 THEN CAST('' AS text)
               WHEN rj.type = CAST('sortkey' AS text)
                AND len(rj.sortkey) = 0
                 THEN CAST(' ALTER SORTKEY NONE' AS text)
               ELSE (CAST(' ALTER COMPOUND SORTKEY (' AS text) || rj.sortkey) || CAST(')' AS text)
             END AS skr,
             CASE
               WHEN len(rj.encode_col) = 0
                 THEN CAST('' AS text)
               ELSE ((CAST(' ALTER COLUMN ' AS text) || split_part(rj.encode_col,
                                                                   CAST(':' AS text),
                                                                   1)) || CAST('" ENCODE ' AS text)) || substring(split_part(rj.encode_col,
                                                                                                                             CAST(':' AS text),
                                                                                                                             2),
                                                                                                                  1,
                                                                                                                  length(split_part(rj.encode_col,
                                                                                                                                    CAST(':' AS text),
                                                                                                                                    2)) - 1)
             END AS enc,
             CASE
               WHEN len(rj.distkey) <> 0
                AND len(rj.sortkey) <> 0
                 THEN CAST(',' AS text)
               ELSE CAST('' AS text)
             END AS comma_dkr_skr,
             CASE
               WHEN (rj.type = CAST('diststyle' AS text)
                 AND CAST(pce0.value AS integer) >= 10)
                 OR (rj.type = CAST('sortkey' AS text)
                 AND CAST(pce12.value AS integer) = 1)
                 OR (rj.type = CAST('encode' AS text)
                 AND CAST(pce14.value AS integer) = 1)
                 THEN CAST('t' AS text)
               ELSE CAST('f' AS text)
             END AS auto_eligible
      FROM (SELECT btrim(CAST(stv_alter_table_recommendations.database AS text)) AS database,
                   btrim(CAST(stv_alter_table_recommendations.schema AS text)) AS schema,
                   btrim(CAST(stv_alter_table_recommendations.table_name AS text)) AS table_name,
                   stv_alter_table_recommendations.table_id,
                   stv_alter_table_recommendations.group_id,
                   stv_alter_table_recommendations.benefit,
                   CASE
                     WHEN stv_alter_table_recommendations.type = 23
                       OR stv_alter_table_recommendations.type = 25
                       THEN CAST('diststyle' AS text)
                     WHEN stv_alter_table_recommendations.type = 24
                       THEN CAST('sortkey' AS text)
                     WHEN stv_alter_table_recommendations.type = 26
                       THEN CAST('encode' AS text)
                     ELSE CAST(NULL AS text)
                   END AS type,
                   json_extract_path_text(CAST(stv_alter_table_recommendations.col_names AS text),
                                          CAST('distkey' AS text)) AS distkey,
                   translate(json_extract_path_text(CAST(stv_alter_table_recommendations.col_names AS text),
                                                    CAST('sortkey' AS text)),
                             CAST('[]' AS text),
                             CAST('' AS text)) AS sortkey,
                   translate(json_extract_path_text(CAST(stv_alter_table_recommendations.col_names AS text),
                                                    CAST('encode' AS text)),
                             CAST('[]' AS text),
                             CAST('' AS text)) AS encode_col
            FROM stv_alter_table_recommendations
            ORDER BY stv_alter_table_recommendations.benefit DESC) AS rj
           LEFT JOIN pg_class_extended AS pce0 ON CAST(rj.table_id AS oid) = pce0.reloid
                                              AND pce0.colnum = 0
           LEFT JOIN pg_class_extended AS pce12 ON CAST(rj.table_id AS oid) = pce12.reloid
                                               AND pce12.colnum = 12
           LEFT JOIN pg_class_extended AS pce14 ON CAST(rj.table_id AS oid) = pce14.reloid
                                               AND pce14.colnum = 14
      WHERE rj.type IS NOT NULL) AS tbl_recos_str

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers