DESIGNER_DESIGN_PROJECTION_ENCODINGS

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

Behavior Type

Immutable

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
    Specifies to 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 all projections in the schema online_sales, as follows:

  • 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.*','');

CREATE PROJECTION online_page_dimension_DBD_1_seg_EncodingDesign /*+createtype(D)*/
(
 online_page_key ENCODING COMMONDELTA_COMP,
 start_date ENCODING DELTAVAL,
 end_date ENCODING DELTAVAL,
 page_number ENCODING DELTAVAL,
 page_description,
 page_type
)
AS
 SELECT online_page_dimension.online_page_key,
        online_page_dimension.start_date,
        online_page_dimension.end_date,
        online_page_dimension.page_number,
        online_page_dimension.page_description,
        online_page_dimension.page_type
 FROM online_sales.online_page_dimension
 ORDER BY online_page_dimension.online_page_key
SEGMENTED BY hash(online_page_dimension.online_page_key) ALL NODES KSAFE 1;


select refresh('online_sales.online_page_dimension');

select make_ahm_now();

DROP PROJECTION online_sales.online_page_dimension CASCADE;

ALTER PROJECTION online_sales.online_page_dimension_DBD_1_seg_EncodingDesign RENAME TO online_page_dimension;

...
(1 row)

See Also

Running Database Designer Programmatically