DESIGNER_DESIGN_PROJECTION_ENCODINGS
Analyzes encoding in the specified projections, creates a script to implement encoding recommendations, and optionally deploys the recommendations.
Behavior Type
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:
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 => SELECT DESIGNER_DESIGN_PROJECTION_ENCODINGS ('mydb.private.*','encodings.sql'); |
destination |
Specifies where to send output, one of the following:
|
deploy |
Boolean that specifies whether to deploy encoding changes. Default: false |
reanalyze‑encodings |
Boolean that specifies whether
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)