Run Database Designer with administration tools

In this procedure, you create a comprehensive design with Database Designer using the Administration Tools interface. If, in the future, you have a query that you want to optimize, you can create an enhanced (incremental) design with additional projections. You can tune these projections specifically for the query you provide. See Incremental design in the Administrator’s Guide for more information.

Follow these steps to create the comprehensive design using Database Designer in Administration Tools:

1. If you are not in Administration Tools, exit the vsql session and access Administration Tools:

   • Type \q to exit vsql.

   • Type admintools to access the Administration Tools Main Menu.

2. Start the database for which you want to create a design.

3. From the Main Menu, click Configuration Menu and then click OK.

4. From the Configuration Menu, click Run Database Designer and then click OK.

5. When the Select a database for design dialog box opens, select VMart and then click OK.

   If you are prompted to enter the password for the database, click OK to bypass the message. Because no password was assigned when you installed the VMart database, you do not need to enter one now.

6. Click OK to accept the default directory for storing Database Designer output and log files.

7. 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.

8. Create a complete initial design. In the Design Type window, click Comprehensive and click OK.

9. Select the schemas. Because the VMart design is a multi-schema database, you can select all three schemas (online_sales, public, and store) for your design. 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 Vertica recommends that you deselect the schemas that contain empty tables before you proceed.

10. In the Design Options window, accept all three options and click OK.

    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. Vertica 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.
    • Deploy design: The new design deploys automatically. During deployment, new projections are added, some existing projections retained, and any necessary existing projections removed. Any new projections are refreshed to populate them with data.

11. Because 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:

    /opt/vertica/examples/VMart_Schema/vmart_queries.sql
    

    The queries in the query file must be delimited with semicolons (;). The last query must end with a semicolon (;).

12. Choose the K-safety value you want and click OK. The design K-Safety determines the number of buddy projections you want database designer to create.

    If you create a comprehensive design on a single node, you are not prompted to enter a K-safety value.

13. In the Optimization Objective window, select Balanced query/load performance to create a design that is balanced between database size and query performance. Click OK.

14. When the informational message displays, click Proceed.

    Database Designer automatically performs these actions:

    • Sets up the design session.

    • Examines table data.

    • Loads queries from the query file you provided (in this example, /opt/vertica/examples/VMart_Schema/vmart_queries.sql).

    • Creates the design.

    Deploys the design or saves a SQL file containing the commands to create the design, based on your selections in the Desgin Options window.

    Depending on system resources, the design process could take several minutes. You should allow this process to complete uninterrupted. If you must cancel the session, use Ctrl+C.

15. When Database Designer finishes, press Enter to return to the Administration Tools menu. Examine the steps taken to create the design. The files are in the directory you specified to store the output and log files. In this example, that directory is /opt/vertica/examples/VMart_Schema. For more information about the script files, see About Database Designer.

For additional information about managing your designs, see Creating a database design in the Administrator’s Guide.