The Database Designer is a tool that analyzes a logical schema definition, sample queries, and sample data and generates a set of projections in the form of an SQL script to be executed after you create the tables but before you load any data. The script creates a minimal set of superprojections to ensure K-Safety, and optionally pre-join projections. In most cases, the projections created by the Database Designer provide excellent query performance within physical constraints. You can, however, write a custom projection script should the Database Designer not meet your needs.

This section provides a detailed description of the Database Designer dialog.

Default: the directory in which you started the Administration Tools

Enter the absolute or relative pathname of the directory containing your sample data files (relative to the current directory when you ran the Administration Tools).

Name each sample data file to match the corresponding table in the logical schema. Case does not matter. For example, if a table is named Stock_Dimension, name the corresponding data file stock_dimension.tbl.

When using multiple data files for a fact table, append _nnn (where nnn is a positive integer in the range 001 to 999) to the filename (not the extension). For example: stock_dimension_001.tbl, stock_dimension_002.tbl, and so forth. In that case, the Database Designer samples only the lowest numbered file.

If the sample fact table data file contains fewer than 100K rows, the Database Designer samples all rows. Otherwise, the Database Designer does up to 1000 uniform seeks reading blocks of 100 rows at a time. The sampling is uniformly distributed to avoid skew problems.

Default: .tbl

Enter the filename extension of the sample data files. The default is the extension used by the sample data files in the Quick Start.

Default: vertical bar (|)

Enter the single character used to delimit column values in the sample data file. The default (vertical bar) is the delimiter used in the sample data files described in the Quick Start. See the COPY command in the SQL Reference Manual for details about specifying a delimiter.

Do not use the backslash character (\) as a delimiter.

Default: backslash lowercase en (\n)

Enter the character string that represents a null value in your sample data file. If you specify an empty field (delete the default \n value), the null data value indicator is two consecutive column delimiters.

The default null indicator in the Database Designer (\n) is the same as the null indicator used in the sample data files described in the Quick Start. See the COPY command in the SQL Reference Manual for details about specifying null values.

Default: 100M

Enter the maximum number of rows that will be stored in the actual fact table at any given time. You can use the letters K and M to mean thousands and millions, respectively. Case does not matter.

The number you specify here is used for cost benefit analysis and does not have to be perfectly accurate.

Default: 1

Enter 0 for a projection script that contains only the projections needed for a functional database.

Enter 1 for a projection script that includes the replicated dimension tables and buddy projections required for a database with K=1 (a database that can function with one node down).

Single node databases?

Three node cluster cannot function with a node down?

This tool runs the Database Designer.

1. On the Configuration Menu, select Create New Database Design.

   

2. Select the database on which to run the Database Designer. The default is the currently active database.

   

3. Specify the pathname for the Database Designer physical schema design script and log files.

   

4. Set the Database Designer parameters.

   

   See the Database Designer Dialog section of this document for detailed descriptions of each input parameter.

   This section provides a detailed description of the Database Designer dialog.

   

   Default: the directory in which you started the Administration Tools

   Enter the absolute or relative pathname of the directory containing your sample data files (relative to the current directory when you ran the Administration Tools).

   Name each sample data file to match the corresponding table in the logical schema. Case does not matter. For example, if a table is named Stock_Dimension, name the corresponding data file stock_dimension.tbl.

   When using multiple data files for a fact table, append _nnn (where nnn is a positive integer in the range 001 to 999) to the filename (not the extension). For example: stock_dimension_001.tbl, stock_dimension_002.tbl, and so forth. In that case, the Database Designer samples only the lowest numbered file.

   If the sample fact table data file contains fewer than 100K rows, the Database Designer samples all rows. Otherwise, the Database Designer does up to 1000 uniform seeks reading blocks of 100 rows at a time. The sampling is uniformly distributed to avoid skew problems.

   

   Default: .tbl

   Enter the filename extension of the sample data files. The default is the extension used by the sample data files in the Quick Start.

   

   Default: vertical bar (|)

   Enter the single character used to delimit column values in the sample data file. The default (vertical bar) is the delimiter used in the sample data files described in the Quick Start. See the COPY command in the SQL Reference Manual for details about specifying a delimiter.

   Do not use the backslash character (\) as a delimiter.

   

   Default: backslash lowercase en (\n)

   Enter the character string that represents a null value in your sample data file. If you specify an empty field (delete the default \n value), the null data value indicator is two consecutive column delimiters.

   The default null indicator in the Database Designer (\n) is the same as the null indicator used in the sample data files described in the Quick Start. See the COPY command in the SQL Reference Manual for details about specifying null values.

   

   Default: 100M

   Enter the maximum number of rows that will be stored in the actual fact table at any given time. You can use the letters K and M to mean thousands and millions, respectively. Case does not matter.

   The number you specify here is used for cost benefit analysis and does not have to be perfectly accurate.

   

   Default: 1

   Enter 0 for a projection script that contains only the projections needed for a functional database.

   Enter 1 for a projection script that includes the replicated dimension tables and buddy projections required for a database with K=1 (a database that can function with one node down).

   Single node databases?

   Three node cluster cannot function with a node down?

5. Choose a Design type.

   

   Create a basic design produces a design that consists only of single-table superprojections.

   A superprojection is a projection that contains every column of a table in the Logical Schema. A table can have multiple superprojections with different sort orders.

   Create a design optimized for queries produces a design that includes pre-join projections based on the sample queries you provide.

   A pre-join projection stores the result set obtained by joining a single fact table in the logical schema (the anchor table) with one or more dimension tables. The result set is typically sorted for optimal query execution performance. Most query-specific projections are pre-joins.

6. If you chose to create an optimized design, enter the pathname of the file containing your sample queries (as described in Prepare a Sample Query Script).

   

7. Several minutes may pass before the following success message appears.

   

8. See Using the Database Designer for information about examining the designer output files.

The tools needed for administering a Vertica database are provided in the form of a graphical user interface that allows you to perform various tasks quickly and easily. The tools also provide a convenient way to connect to a database using vsql. Always run the Administration Tools on the Administration Host if possible.

$ /opt/vertica/bin/adminTools

See the Administration Tools Reference for a complete description.