Step 5: Create a Comprehensive Design
This step is shared between the Admin Guide and the Getting Started Guide.
In this procedure you'll create a comprehensive design using Database Designer through the Administration Tools interface. These steps assumes that you have already performed the following prerequisite steps:
- Set up the example environment
- Created the example database
- Defined the database schema
- Loaded the data
Note: To create a comprehensive design using Database Designer programmatically, see About Running Database Designer Programmatically in Analyzing Data. If you have a query you want to optimize after you create a comprehensive design, you can create an incremental design later. See Incremental Design for details.
Create the Comprehensive Design Using the Database Designer
-
To exit the vsql session and return to the Main Menu in the Administration Tools, type
\q
.Alternatively, restart the Administration Tools:
$
/opt/vertica/bin/admintools
- From the Main Menu, click Configuration Menu and click OK.
- From the Configuration Menu, click Run Database Designer and click OK.
-
Select vmartdb as the database and click OK.
If you are asked to enter the password for the database, click OK to bypass. No password was assigned in Step 2: Create the Example Database, so you do not need to enter one now.
-
Click OK to accept the default directory (
/tmp/examples
, unless you changed it) for storing Database Designer output and log files. Note this location.Note: If you choose to not deploy your design now, Database Designer saves the SQL script to deploy the design in the default directory where you can review and manually deploy it later.
- In the Database Designer window, enter a name for the design, for example, vmart_design, and click OK. Design names can contain only alphanumeric characters or underscores. No other special characters are allowed.
- To create a complete initial design, in the Design Type window, click Comprehensive and click OK.
-
Because the Vmart design is a multi-schema database, select all three schemas for your design, and click OK.
If you include a schema that contains tables without data, the Administration Tools notifies you that designing for tables without data could be suboptimal. You can choose to continue, but OpenText recommends that you click Cancel and deselect the schemas that contain empty tables before you proceed.
-
In the Design Options window, because the Vmart design is a multi-schema database, accept all three options (described below) and click OK.
Generally, you enable all three options because Database Designer is best positioned to generate a new comprehensive design and create a complete set of projections for the tables in the selected schema. The three options are:
-
Optimize with queries: Supplying the Database Designer with queries is especially important if you want to optimize the database design for query performance.
Database Designer does not impose hard limits to the number of queries or tables it accepts as input. However, it is limited by system resources, concurrent loads, and query/schema complexity. OpenText recommends that you limit the design input to 100 queries.
-
Update statistics: Accurate statistics help the Database Designer choose the best strategy for data compression. If you select this option, the database statistics are updated to maximize design quality.
Updating statistics takes time and resources, so if the current statistics are up to date, this step is unnecessary. When in doubt, update statistics.
- Deploy design: The new design is automatically deployed, which means that during deployment, new projections are added, some existing projections might be retained, and any unnecessary existing projections are removed. Any new projections are refreshed so that they are populated with data.
Note: For large databases, a full design session could take a long time, but it is best to allow this process to complete uninterrupted. If the session must be canceled, use Ctrl+C.
-
-
If you selected the Optimize with queries option, you must enter the full path to the file containing the queries that will be run on your database. In this example it is:
/tmp/examples/vmart_queries.sql
The queries in the query file must be delimited with a semicolon (;).
Note: Although there is no hard limit to the number of queries or tables you can provide as input to a comprehensive design, Database Designer is limited by system resources, concurrent loads, and query/schema complexity. OpenText recommends that you limit the design input to 100 queries.
-
Choose the K-safety value you want. This example uses 1. Click OK.
Note: If you are creating a comprehensive design on a single node, you are not asked to enter a K-safety value.
-
Choose Balanced for the Database Designer's design priority and click OK.
The design priorities are:
- Balanced query/load performance tells Database Designer to create a design that is balanced between database size and query performance.
- Query load performance creates a design focused on faster query performance, which might recommend additional projections. These projections could result in a larger database storage size.
- Load performance is optimized for loads, minimizing database size, potentially at the expense of query performance.
-
When the informational message displays, click Proceed.
Database Designer:
- Sets up the design session
- Examines table data
- Loads queries from the query file you provided
- Creates the design
- Deploys the design or saves a SQL file containing the design, depending on what you selected for the Deploy design option in step 9.
Depending on system resources, the design process could take several minutes.
- When Database Designer finishes, press Enter to return to the Administration Tools menu.
-
After you have created your design, query the system table DESIGN_STATUS to see the steps taken to create the design. If you also deployed the design, those steps are listed in the system table DEPLOY_STATUS:
=> SELECT * FROM V_MONITOR.DESIGN_STATUS;vmartdb=> SELECT * FROM V_MONITOR.DEPLOY_STATUS;
Note: When you run Database Designer using the Administration Tools, it creates a backup of the current design of your database before deploying the new design. This backup is stored in the directory you specified in step 5 and is named
catalog_dump.sql
. - Now you are ready to connect to the database and run simple queries.