This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Database Designer functions
Database Designer functions perform the following operations, generally performed in the following order:
-
Create a design.
-
Set design properties.
-
Populate a design.
-
Create design and deployment scripts.
-
Get design data.
-
Clean up.
Important
You can also use meta-function
DESIGNER_SINGLE_RUN, which encapsulates all of these steps with a single call. The meta-function iterates over all queries within a specified timespan, and returns with a design ready for deployment.
For detailed information, see Workflow for running Database Designer programmatically. For information on required privileges, see Privileges for running Database Designer functions
Caution
Before running Database Designer functions on an existing schema, back up the current design by calling
EXPORT_CATALOG.
Create a design
DESIGNER_CREATE_DESIGN directs Database Designer to create a design.
Set design properties
The following functions let you specify design properties:
Populate a design
The following functions let you add tables and queries to your Database Designer design:
Create design and deployment scripts
The following functions populate the Database Designer workspace and create design and deployment scripts. You can also analyze statistics, deploy the design automatically, and drop the workspace after the deployment:
Reset a design
DESIGNER_RESET_DESIGN discards all the run-specific information of the previous Database Designer build or deployment of the specified design but retains its configuration.
Get design data
The following functions display information about projections and scripts that the Database Designer created:
Cleanup
The following functions cancel any running Database Designer operation or drop a Database Designer design and all its contents:
1 - DESIGNER_ADD_DESIGN_QUERIES
Reads and evaluates queries from an input file, and adds the queries that it accepts to the specified design.
Reads and evaluates queries from an input file, and adds the queries that it accepts to the specified design. All accepted queries are assigned a weight of 1.
The following requirements apply:
-
All queried tables must previously be added to the design with DESIGNER_ADD_DESIGN_TABLES.
-
If the design type is incremental, the Database Designer reads only the first 100 queries in the input file, and ignores all queries beyond that number.
All accepted queries are added to the system table DESIGN_QUERIES.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_ADD_DESIGN_QUERIES ( 'design-name', 'queries-file' [, return-results] )
Parameters
design-name- Name of the target design.
queries-file- Absolute path and name of the file that contains the queries to evaluate, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.
return-results- Boolean, optionally specifies whether to return results of the add operation to standard output. If set to true, Database Designer returns the following results:
-
Number of accepted queries
-
Number of queries referencing non-design tables
-
Number of unsupported queries
-
Number of illegal queries
Privileges
Non-superuser: design creator with all privileges required to execute the queries in input-file.
Errors
Database Designer returns an error in the following cases:
-
The query contains illegal syntax.
-
The query references:
-
DELETE or UPDATE query has one or more subqueries.
-
INSERT query does not include a SELECT clause.
-
Database Designer cannot optimize the query.
Examples
The following example adds queries from vmart_queries.sql to the VMART_DESIGN design. This file contains nine queries. The statement includes a third argument of true, so Database Designer returns results of the add operation:
=> SELECT DESIGNER_ADD_DESIGN_QUERIES ('VMART_DESIGN', '/tmp/examples/vmart_queries.sql', 'true');
...
DESIGNER_ADD_DESIGN_QUERIES
----------------------------------------------------
Number of accepted queries =9
Number of queries referencing non-design tables =0
Number of unsupported queries =0
Number of illegal queries =0
(1 row)
See also
Running Database Designer programmatically
2 - DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS
Executes the specified query and evaluates results in the following columns:.
Executes the specified query and evaluates results in the following columns:
-
QUERY_TEXT (required): Text of potential design queries.
-
QUERY_WEIGHT (optional): The weight assigned to each query that indicates its importance relative to other queries, a real number >0 and ≤ 1. Database Designer uses this setting when creating the design to prioritize the query. If DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS returns any results that omit this value, Database Designer sets their weight to 1.
After evaluating the queries in QUERY_TEXT, DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS adds all accepted queries to the design. An unlimited number of queries can be added to the design.
Before you add queries to a design, you must add the queried tables with
DESIGNER_ADD_DESIGN_TABLES.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS ( 'design-name', 'query' )
Parameters
design-name- Name of the target design.
query- A valid SQL query whose results contain columns named
QUERY_TEXT and, optionally, QUERY_WEIGHT.
Privileges
Non-superuser: design creator with all privileges required to execute the specified query, and all queries returned by this function
Errors
Database Designer returns an error in the following cases:
-
The query contains illegal syntax.
-
The query references:
-
DELETE or UPDATE query has one or more subqueries.
-
INSERT query does not include a SELECT clause.
-
Database Designer cannot optimize the query.
Examples
The following example queries the system table
QUERY_REQUESTS for all long-running queries (> 1 million microseconds) and adds them to the VMART_DESIGN design. The query returns no information on query weights, so all queries are assigned a weight of 1:
=> SELECT DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS ('VMART_DESIGN',
'SELECT request as query_text FROM query_requests where request_duration_ms > 1000000 AND request_type =
''QUERY'';');
See also
Running Database Designer programmatically
3 - DESIGNER_ADD_DESIGN_QUERY
Reads and parses the specified query, and if accepted, adds it to the design.
Reads and parses the specified query, and if accepted, adds it to the design. Before you add queries to a design, you must add the queried tables with
DESIGNER_ADD_DESIGN_TABLES.
All accepted queries are added to the system table
DESIGN_QUERIES.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_ADD_DESIGN_QUERY ( 'design-name', 'design-query' [, query-weight] )
Parameters
design-name- Name of the target design.
design-query- Executable SQL query.
query-weight- Optionally assigns a weight to each query that indicates its importance relative to other queries, a real number >0 and ≤ 1. Database Designer uses this setting to prioritize queries in the design .
If you omit this parameter, Database Designer assigns a weight of 1.
Privileges
Non-superuser: design creator with all privileges required to execute the specified query
Errors
Database Designer returns an error in the following cases:
-
The query contains illegal syntax.
-
The query references:
-
DELETE or UPDATE query has one or more subqueries.
-
INSERT query does not include a SELECT clause.
-
Database Designer cannot optimize the query.
Examples
The following example adds the specified query to the VMART_DESIGN design and assigns that query a weight of 0.5:
=> SELECT DESIGNER_ADD_DESIGN_QUERY (
'VMART_DESIGN',
'SELECT customer_name, customer_type FROM customer_dimension ORDER BY customer_name ASC;', 0.5
);
See also
Running Database Designer programmatically
4 - DESIGNER_ADD_DESIGN_TABLES
Adds the specified tables to a design.
Adds the specified tables to a design. You must run DESIGNER_ADD_DESIGN_TABLES before adding design queries to the design. If no tables are added to the design, Vertica does not accept design queries.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_ADD_DESIGN_TABLES ( 'design-name', '[ table-spec[,...] ]' [, 'analyze-statistics'] )
Parameters
design-name- Name of the Database Designer design.
table-spec[,...]- One or more comma-delimited arguments that specify which tables to add to the design, where each
table-spec argument can specify tables as follows:
If set to an empty string, Vertica adds all tables in the database to which the user has access.
analyze-statistics- Boolean that optionally specifies whether to run
ANALYZE_STATISTICS after adding the specified tables to the design, by default set to false.
Accurate statistics help Database Designer optimize compression and query performance. Updating statistics takes time and resources.
Privileges
Non-superuser: design creator with USAGE privilege on the design table schema and owner of the design table
Examples
The following example adds to design VMART_DESIGN all tables from schemas online_sales and store, and analyzes statistics for those tables:
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN', 'online_sales.*, store.*','true');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
7
(1 row)
See also
Running Database Designer programmatically
5 - DESIGNER_CANCEL_POPULATE_DESIGN
Cancels population or deployment operation for the specified design if it is currently running.
Cancels population or deployment operation for the specified design if it is currently running. When you cancel a deployment, the Database Designer cancels the projection refresh operation. It does not roll back projections that it already deployed and refreshed.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_CANCEL_POPULATE_DESIGN ( 'design-name' )
Parameters
design-name- Name of the design operation to cancel.
Privileges
Non-superuser: design creator
Examples
The following example cancels a currently running design for VMART_DESIGN and then drops the design:
=> SELECT DESIGNER_CANCEL_POPULATE_DESIGN ('VMART_DESIGN');
=> SELECT DESIGNER_DROP_DESIGN ('VMART_DESIGN', 'true');
See also
Running Database Designer programmatically
6 - DESIGNER_CREATE_DESIGN
Creates a design with the specified name.
Creates a design with the specified name.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_CREATE_DESIGN ( 'design-name' )
Parameters
design-name- Name of the design to create, can contain only alphanumeric and underscore (_) characters.
Two users cannot have designs with the same name at the same time.
Privileges
Database Designer system views
If any of the following
V_MONITOR tables do not already exist from previous designs, DESIGNER_CREATE_DESIGN creates them:
Examples
The following example creates the design VMART_DESIGN:
=> SELECT DESIGNER_CREATE_DESIGN('VMART_DESIGN');
DESIGNER_CREATE_DESIGN
------------------------
0
(1 row)
See also
Running Database Designer programmatically
7 - DESIGNER_DESIGN_PROJECTION_ENCODINGS
Analyzes encoding in the specified projections, creates a script to implement encoding recommendations, and optionally deploys the recommendations.
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:
Default: false
Privileges
Superuser, or DBDUSER with the following privileges:
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)
See also
Running Database Designer programmatically
8 - DESIGNER_DROP_ALL_DESIGNS
Removes all Database Designer-related schemas associated with the current user.
Removes all Database Designer-related schemas associated with the current user. Use this function to remove database objects after one or more Database Designer sessions complete execution.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_DROP_ALL_DESIGNS()
Parameters
None.
Privileges
Non-superuser: design creator
Examples
The following example removes all schema and their contents associated with the current user. DESIGNER_DROP_ALL_DESIGNS returns the number of designs dropped:
=> SELECT DESIGNER_DROP_ALL_DESIGNS();
DESIGNER_DROP_ALL_DESIGNS
---------------------------
2
(1 row)
See also
9 - DESIGNER_DROP_DESIGN
Removes the schema associated with the specified design and all its contents.
Removes the schema associated with the specified design and all its contents. Use DESIGNER_DROP_DESIGN after a Database Designer design or deployment completes successfully. You must also use it to drop a design before creating another one under the same name.
To drop all designs that you created, use
DESIGNER_DROP_ALL_DESIGNS.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_DROP_DESIGN ( 'design-name' [, force-drop ] )
Parameters
design-name- Name of the design to drop.
force-drop- Boolean that overrides any dependencies that otherwise prevent Vertica from executing this function—for example, the design is in use or is currently being deployed. If you omit this parameter, Vertica sets it to
false.
Privileges
Non-superuser: design creator
Examples
The following example deletes the Database Designer design VMART_DESIGN and all its contents:
=> SELECT DESIGNER_DROP_DESIGN ('VMART_DESIGN');
See also
Running Database Designer programmatically
10 - DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS
Displays the DDL statements that define the design projections to standard output.
Displays the DDL statements that define the design projections to standard output.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS ( 'design-name' )
Parameters
design-name- Name of the target design.
Privileges
Superuseror DBDUSER
Examples
The following example returns the design projection DDL statements for vmart_design:
=> SELECT DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS('vmart_design');
CREATE PROJECTION customer_dimension_DBD_1_rep_VMART_DESIGN /*+createtype(D)*/
(
customer_key ENCODING DELTAVAL,
customer_type ENCODING AUTO,
customer_name ENCODING AUTO,
customer_gender ENCODING REL,
title ENCODING AUTO,
household_id ENCODING DELTAVAL,
customer_address ENCODING AUTO,
customer_city ENCODING AUTO,
customer_state ENCODING AUTO,
customer_region ENCODING AUTO,
marital_status ENCODING AUTO,
customer_age ENCODING DELTAVAL,
number_of_children ENCODING BLOCKDICT_COMP,
annual_income ENCODING DELTARANGE_COMP,
occupation ENCODING AUTO,
largest_bill_amount ENCODING DELTAVAL,
store_membership_card ENCODING BLOCKDICT_COMP,
customer_since ENCODING DELTAVAL,
deal_stage ENCODING AUTO,
deal_size ENCODING DELTARANGE_COMP,
last_deal_update ENCODING DELTARANGE_COMP
)
AS
SELECT customer_key,
customer_type,
customer_name,
customer_gender,
title,
household_id,
customer_address,
customer_city,
customer_state,
customer_region,
marital_status,
customer_age,
number_of_children,
annual_income,
occupation,
largest_bill_amount,
store_membership_card,
customer_since,
deal_stage,
deal_size,
last_deal_update
FROM public.customer_dimension
ORDER BY customer_gender,
annual_income
UNSEGMENTED ALL NODES;
CREATE PROJECTION product_dimension_DBD_2_rep_VMART_DESIGN /*+createtype(D)*/
(
...
See also
DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT
11 - DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT
Displays the deployment script for the specified design to standard output.
Displays the deployment script for the specified design to standard output. If the design is already deployed, Vertica ignores this function.
To output only the CREATE PROJECTION commands in a design script, use
DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT ( 'design-name' )
Parameters
design-name- Name of the target design.
Privileges
Non-superuser: design creator
Examples
The following example displays the deployment script for VMART_DESIGN:
=> SELECT DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT('VMART_DESIGN');
CREATE PROJECTION customer_dimension_DBD_1_rep_VMART_DESIGN /*+createtype(D)*/
...
CREATE PROJECTION product_dimension_DBD_2_rep_VMART_DESIGN /*+createtype(D)*/
...
select refresh('public.customer_dimension,
public.product_dimension,
public.promotion.dimension,
public.date_dimension');
select make_ahm_now();
DROP PROJECTION public.customer_dimension_super CASCADE;
DROP PROJECTION public.product_dimension_super CASCADE;
...
See also
DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS
12 - DESIGNER_RESET_DESIGN
Discards all run-specific information of the previous Database Designer build or deployment of the specified design but keeps its configuration.
Discards all run-specific information of the previous Database Designer build or deployment of the specified design but keeps its configuration. You can make changes to the design as needed, for example, by changing parameters or adding additional tables and/or queries, before running the design again.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_RESET_DESIGN ( 'design-name' )
Parameters
design-name- Name of the design to reset.
Privileges
Non-superuser: design creator
Examples
The following example resets the Database Designer design VMART_DESIGN:
=> SELECT DESIGNER_RESET_DESIGN ('VMART_DESIGN');
13 - DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY
Populates the design and creates the design and deployment scripts.
Populates the design and creates the design and deployment scripts. DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY can also analyze statistics, deploy the design, and drop the workspace after the deployment.
The files output by this function have the permissions 666 or rw-rw-rw-, which allows any Linux user on the node to read or write to them. It is highly recommended that you keep the files in a secure directory.
Caution
DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY does not create a backup copy of the current design before deploying the new design. Before running this function, back up the existing schema design with
EXPORT_CATALOG.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY (
'design-name',
'output-design-file',
'output-deployment-file'
[ , 'analyze-statistics']
[ , 'deploy']
[ , 'drop-design-workspace']
[ , 'continue-after-error']
)
Parameters
design-name- Name of the design to populate and deploy.
output-design-filename- Absolute path and name of the file to contain DDL statements that create design projections, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.
output-deployment-filename- Absolute path and name of the file to contain the deployment script, on the local file system of the node where the session is connected, or another file system or object store that Vertica supports.
analyze-statistics- Specifies whether to collect or refresh statistics for the tables before populating the design. If set to true, Vertica Invokes ANALYZE_STATISTICS. Accurate statistics help Database Designer optimize compression and query performance. However, updating statistics requires time and resources.
Default: false
- deploy
- Specifies whether to deploy the Database Designer design using the deployment script created by this function.
Default: true
drop-design-workspace- Specifies whether to drop the design workspace after the design is deployed.
Default: true
continue-after-error- Specifies whether DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY continues to run after an error occurs. By default, an error causes this function to terminate.
Default: false
Privileges
Non-superuser: design creator with WRITE privileges on storage locations of design and deployment scripts
Requirements
Before calling this function, you must:
-
Create a design, a logical schema with tables.
-
Associate tables with the design.
-
Load queries to the design.
-
Set design properties (K-safety level, mode, and policy).
Examples
The following example creates projections for and deploys the VMART_DESIGN design, and analyzes statistics about the design tables.
=> SELECT DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY (
'VMART_DESIGN',
'/tmp/examples/vmart_design_files/design_projections.sql',
'/tmp/examples/vmart_design_files/design_deploy.sql',
'true',
'true',
'false',
'false'
);
See also
Running Database Designer programmatically
14 - DESIGNER_SET_DESIGN_KSAFETY
Sets K-safety for a comprehensive design and stores the K-safety value in the DESIGNS table.
Sets K-safety for a comprehensive design and stores the K-safety value in the
DESIGNS table. Database Designer ignores this function for incremental designs.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_SET_DESIGN_KSAFETY ( 'design-name' [, k-level ] )
Parameters
design-name- Name of the design for which you want to set the K-safety value, type VARCHAR.
k-level- An integer between 0 and 2 that specifies the level of K-safety for the target design. This value must be compatible with the number of nodes in the database cluster:
-
k-level = 0: ≥ 1 nodes
-
k-level = 1: ≥ 3 nodes
-
k-level = 2: ≥ 5 nodes
If you omit this parameter, Vertica sets K-safety for this design to 0 or 1, according to the number of nodes: 1 if the cluster contains ≥ 3 nodes, otherwise 0.
If you are a DBADMIN user and k-level differs from system K-safety, Vertica changes system K-safety as follows:
-
If k-level is less than system K-safety, Vertica changes system K-safety to the lower level after the design is deployed.
-
If k-level is greater than system K-safety and is valid for the database cluster, Vertica creates the required number of buddy projections for the tables in this design. If the design applies to all database tables, or all tables in the database have the required number of buddy projections, Database Designer changes system K-safety to k-level.
If the design excludes some database tables and the number of their buddy projections is less than k-level, Database Designer leaves system K-safety unchanged. Instead, it returns a warning and indicates which tables need new buddy projections in order to adjust system K-safety.
If you are a DBDUSER, Vertica ignores this parameter.
Privileges
Non-superuser: design creator
Examples
The following example set K-safety for the VMART_DESIGN design to 1:
=> SELECT DESIGNER_SET_DESIGN_KSAFETY('VMART_DESIGN', 1);
See also
Running Database Designer programmatically
15 - DESIGNER_SET_DESIGN_TYPE
Specifies whether Database Designer creates a comprehensive or incremental design.
Specifies whether Database Designer creates a comprehensive or incremental design. DESIGNER_SET_DESIGN_TYPE stores the design mode in the
DESIGNS table.
Important
If you do not explicitly set a design mode with this function, Database Designer creates a comprehensive design.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_SET_DESIGN_TYPE ( 'design-name', 'mode' )
Parameters
design-name- Name of the target design.
mode- Name of the mode that Database Designer should use when designing the database, one of the following:
-
COMPREHENSIVE: Creates an initial or replacement design for all tables in the specified schemas. You typically create a comprehensive design for a new database.
-
INCREMENTAL: Modifies an existing design with additional projection that are optimized for new or modified queries.
Note
Incremental designs always inherit the K-safety value of the database.
For more information, see Design Types.
Privileges
Non-superuser: design creator
Examples
The following examples show the two design mode options for the VMART_DESIGN design:
=> SELECT DESIGNER_SET_DESIGN_TYPE(
'VMART_DESIGN',
'COMPREHENSIVE');
DESIGNER_SET_DESIGN_TYPE
--------------------------
0
(1 row)
=> SELECT DESIGNER_SET_DESIGN_TYPE(
'VMART_DESIGN',
'INCREMENTAL');
DESIGNER_SET_DESIGN_TYPE
--------------------------
0
(1 row)
See also
Running Database Designer programmatically
16 - DESIGNER_SET_OPTIMIZATION_OBJECTIVE
Valid only for comprehensive database designs, specifies the optimization objective Database Designer uses.
Valid only for comprehensive database designs, specifies the optimization objective Database Designer uses. Database Designer ignores this function for incremental designs.
DESIGNER_SET_OPTIMIZATION_OBJECTIVE stores the optimization objective in the
DESIGNS table.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_SET_OPTIMIZATION_OBJECTIVE ( 'design-name', 'policy' )
Parameters
design-name- Name of the target design.
policy- Specifies the design's optimization policy, one of the following:
-
QUERY: Optimize for query performance. This can result in a larger database storage footprint because additional projections might be created.
-
LOAD: Optimize for load performance so database size is minimized. This can result in slower query performance.
-
BALANCED: Balance the design between query performance and database size.
Privileges
Non-superuser: design creator
Examples
The following example sets the optimization objective option for the VMART_DESIGN design: to QUERY:
=> SELECT DESIGNER_SET_OPTIMIZATION_OBJECTIVE( 'VMART_DESIGN', 'QUERY');
DESIGNER_SET_OPTIMIZATION_OBJECTIVE
------------------------------------
0
(1 row)
See also
Running Database Designer programmatically
17 - DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS
Specifies whether a design can include unsegmented projections.
Specifies whether a design can include unsegmented projections. Vertica ignores this function on a one-node cluster, where all projections must be unsegmented.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS ( 'design-name', unsegmented )
Parameters
design-name- Name of the target design.
unsegmented - Boolean that specifies whether Database Designer can propose unsegmented projections for tables in this design. When you create a design, the
propose_unsegmented_projections value in system table
DESIGNS for this design is set to true. If DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS sets this value to false, Database Designer proposes only segmented projections.
Privileges
Non-superuser: design creator
Examples
The following example specifies that Database Designer can propose only segmented projections for tables in the design VMART_DESIGN:
=> SELECT DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS('VMART_DESIGN', false);
See also
Running Database Designer programmatically
18 - DESIGNER_SINGLE_RUN
Evaluates all queries that completed execution within the specified timespan, and returns with a design that is ready for deployment.
Evaluates all queries that completed execution within the specified timespan, and returns with a design that is ready for deployment. This design includes projections that are recommended for optimizing the evaluated queries. Unless you redirect output, DESIGNER_SINGLE_RUN returns the design to stdout.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_SINGLE_RUN ('interval')
interval- Specifies an interval of time that precedes the meta-function call. Database Designer evaluates all queries that ran to completion over the specified interval.
Privileges
Superuser or DBUSER
Examples
-----------------------------------------------------------------------
-- SSBM dataset test
-----------------------------------------------------------------------
-- create ssbm schema
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_schema.sql' > /dev/null 2>&1
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_constraints.sql' > /dev/null 2>&1
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_funcdeps.sql' > /dev/null 2>&1
-- run these queries
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_queries.sql' > /dev/null 2>&1
-- Run single API
select designer_single_run('1 minute');
...
designer_single_run
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROJECTION public.part_DBD_1_rep_SingleDesign /*+createtype(D)*/
(
p_partkey ENCODING AUTO,
p_name ENCODING AUTO,
p_mfgr ENCODING AUTO,
p_category ENCODING AUTO,
p_brand1 ENCODING AUTO,
p_color ENCODING AUTO,
p_type ENCODING AUTO,
p_size ENCODING AUTO,
p_container ENCODING AUTO
)
AS
SELECT p_partkey,
p_name,
p_mfgr,
p_category,
p_brand1,
p_color,
p_type,
p_size,
p_container
FROM public.part
ORDER BY p_partkey
UNSEGMENTED ALL NODES;
CREATE PROJECTION public.supplier_DBD_2_rep_SingleDesign /*+createtype(D)*/
(
s_suppkey ENCODING AUTO,
s_name ENCODING AUTO,
s_address ENCODING AUTO,
s_city ENCODING AUTO,
s_nation ENCODING AUTO,
s_region ENCODING AUTO,
s_phone ENCODING AUTO
)
AS
SELECT s_suppkey,
s_name,
s_address,
s_city,
s_nation,
s_region,
s_phone
FROM public.supplier
ORDER BY s_suppkey
UNSEGMENTED ALL NODES;
CREATE PROJECTION public.customer_DBD_3_rep_SingleDesign /*+createtype(D)*/
(
c_custkey ENCODING AUTO,
c_name ENCODING AUTO,
c_address ENCODING AUTO,
c_city ENCODING AUTO,
c_nation ENCODING AUTO,
c_region ENCODING AUTO,
c_phone ENCODING AUTO,
c_mktsegment ENCODING AUTO
)
AS
SELECT c_custkey,
c_name,
c_address,
c_city,
c_nation,
c_region,
c_phone,
c_mktsegment
FROM public.customer
ORDER BY c_custkey
UNSEGMENTED ALL NODES;
CREATE PROJECTION public.dwdate_DBD_4_rep_SingleDesign /*+createtype(D)*/
(
d_datekey ENCODING AUTO,
d_date ENCODING AUTO,
d_dayofweek ENCODING AUTO,
d_month ENCODING AUTO,
d_year ENCODING AUTO,
d_yearmonthnum ENCODING AUTO,
d_yearmonth ENCODING AUTO,
d_daynuminweek ENCODING AUTO,
d_daynuminmonth ENCODING AUTO,
d_daynuminyear ENCODING AUTO,
d_monthnuminyear ENCODING AUTO,
d_weeknuminyear ENCODING AUTO,
d_sellingseason ENCODING AUTO,
d_lastdayinweekfl ENCODING AUTO,
d_lastdayinmonthfl ENCODING AUTO,
d_holidayfl ENCODING AUTO,
d_weekdayfl ENCODING AUTO
)
AS
SELECT d_datekey,
d_date,
d_dayofweek,
d_month,
d_year,
d_yearmonthnum,
d_yearmonth,
d_daynuminweek,
d_daynuminmonth,
d_daynuminyear,
d_monthnuminyear,
d_weeknuminyear,
d_sellingseason,
d_lastdayinweekfl,
d_lastdayinmonthfl,
d_holidayfl,
d_weekdayfl
FROM public.dwdate
ORDER BY d_datekey
UNSEGMENTED ALL NODES;
CREATE PROJECTION public.lineorder_DBD_5_rep_SingleDesign /*+createtype(D)*/
(
lo_orderkey ENCODING AUTO,
lo_linenumber ENCODING AUTO,
lo_custkey ENCODING AUTO,
lo_partkey ENCODING AUTO,
lo_suppkey ENCODING AUTO,
lo_orderdate ENCODING AUTO,
lo_orderpriority ENCODING AUTO,
lo_shippriority ENCODING AUTO,
lo_quantity ENCODING AUTO,
lo_extendedprice ENCODING AUTO,
lo_ordertotalprice ENCODING AUTO,
lo_discount ENCODING AUTO,
lo_revenue ENCODING AUTO,
lo_supplycost ENCODING AUTO,
lo_tax ENCODING AUTO,
lo_commitdate ENCODING AUTO,
lo_shipmode ENCODING AUTO
)
AS
SELECT lo_orderkey,
lo_linenumber,
lo_custkey,
lo_partkey,
lo_suppkey,
lo_orderdate,
lo_orderpriority,
lo_shippriority,
lo_quantity,
lo_extendedprice,
lo_ordertotalprice,
lo_discount,
lo_revenue,
lo_supplycost,
lo_tax,
lo_commitdate,
lo_shipmode
FROM public.lineorder
ORDER BY lo_suppkey
UNSEGMENTED ALL NODES;
(1 row)
19 - DESIGNER_WAIT_FOR_DESIGN
Waits for completion of operations that are populating and deploying the design.
Waits for completion of operations that are populating and deploying the design. Ctrl+C cancels this operation and returns control to the user.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Behavior type
Volatile
Syntax
DESIGNER_WAIT_FOR_DESIGN ( 'design-name' )
Parameters
design-name- Name of the running design.
Privileges
Superuser, or DBDUSER with USAGE privilege on the design schema
Examples
The following example requests to wait for the currently running design of VMART_DESIGN to complete:
=> SELECT DESIGNER_WAIT_FOR_DESIGN ('VMART_DESIGN');
See also