DESIGNER_DESIGN_PROJECTION_ENCODINGS

Analyzes encoding in the specified projections, creates a script to implement encoding recommendations, and optionally deploys the recommendations.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

DESIGNER_DESIGN_PROJECTION_ENCODINGS ( '[ proj-spec[,... ] ]', '[destination]' [, 'deploy'] [, 'reanalyze-encodings'] )

Parameters

proj-spec[,...]

One or more comma-delimited projections to add to the design. Each projection can be specified in one of the following ways:

[[schema.]table.]projection
Specifies to analyze projection.
schema.*
Specifies to analyze all projections in the named schema.
[schema.]table
Specifiesto analyze all projections of the named table.

If set to an empty string, Vertica analyzes all projections in the database to which the user has access.

For example, the following statement specifies to analyze all projections in schema private, and send the results to the file encodings.sql:

=> SELECT DESIGNER_DESIGN_PROJECTION_ENCODINGS ('mydb.private.*','encodings.sql');

destination

Specifies where to send output, one of the following:

Empty string ('') writes the script to standard output.
Pathname of a SQL output file. If you specify a file that does not exist, the function creates one. If you specify only a file name, Vertica creates it in the catalog directory. If the file already exists, the function silently overwrites its contents.

deploy

Boolean that specifies whether to deploy encoding changes.

Default: false

reanalyze-encodings

Boolean that specifies whether DESIGNER_DESIGN_PROJECTION_ENCODINGS analyzes encodings in a projection where all columns are already encoded:

false: Analyzes no columns and generates no recommendations if all columns are encoded.
true: Ignores existing encodings and generates recommendations.

Default: false

Privileges

Superuser, or DBDUSER with the following privileges:

OWNER of all projections to analyze
USAGE privilege on the schema for the specified projections

Examples

The following example requests that Database Designer analyze encodings of the table online_sales.call_center_dimension:

The second parameter destination is set to an empty string, so the script is sent to standard output (shown truncated below).
The last two parameters deploy and reanalyze-encodings are omitted, so Database Designer does not execute the script or reanalyze existing encodings:

=> SELECT DESIGNER_DESIGN_PROJECTION_ENCODINGS ('online_sales.call_center_dimension','');

              DESIGNER_DESIGN_PROJECTION_ENCODINGS
----------------------------------------------------------------

CREATE PROJECTION call_center_dimension_DBD_1_seg_EncodingDesign /*+createtype(D)*/
(
 call_center_key ENCODING COMMONDELTA_COMP,
 cc_closed_date,
 cc_open_date,
 cc_name ENCODING ZSTD_HIGH_COMP,
 cc_class ENCODING ZSTD_HIGH_COMP,
 cc_employees,
 cc_hours ENCODING ZSTD_HIGH_COMP,
 cc_manager ENCODING ZSTD_HIGH_COMP,
 cc_address ENCODING ZSTD_HIGH_COMP,
 cc_city ENCODING ZSTD_COMP,
 cc_state ENCODING ZSTD_FAST_COMP,
 cc_region ENCODING ZSTD_HIGH_COMP
)
AS
 SELECT call_center_dimension.call_center_key,
        call_center_dimension.cc_closed_date,
        call_center_dimension.cc_open_date,
        call_center_dimension.cc_name,
        call_center_dimension.cc_class,
        call_center_dimension.cc_employees,
        call_center_dimension.cc_hours,
        call_center_dimension.cc_manager,
        call_center_dimension.cc_address,
        call_center_dimension.cc_city,
        call_center_dimension.cc_state,
        call_center_dimension.cc_region
 FROM online_sales.call_center_dimension
 ORDER BY call_center_dimension.call_center_key
SEGMENTED BY hash(call_center_dimension.call_center_key) ALL NODES KSAFE 1;

select refresh('online_sales.call_center_dimension');

select make_ahm_now();

DROP PROJECTION online_sales.call_center_dimension CASCADE;

ALTER PROJECTION online_sales.call_center_dimension_DBD_1_seg_EncodingDesign RENAME TO call_center_dimension;
(1 row)