Overview of database designer

• 1: Database designer

1 - Database designer

About database designer

Use database designer to create designs that provide excellent performance and specific queries while using disk space efficiently. A design is a physical storage plan that optimizes query performance.

The Database designer:

• Analyzes your logical schema, sample data, and sample queries.
• Creates a physical schema design (a set of projections) that can be deployed manually.
• Does not require specialized database knowledge.
• Can be run and rerun any time for additional optimization without stopping the database.
• Uses strategies to provide optimal query performance and data compression.

The database designer recommends projection statements for all tables referenced in the selected queries to ensure optimal query performance. Up to 100 queries can be provided as input.

Select the More options button on the Home page. Choose Database designer. The Database designer page appears with the Projections and Designs tabs.

Only administrators have the privilege to execute the database designer.

Design types

Using the database designer, you can create two distinct design types: comprehensive or incremental.

Comprehensive design

A comprehensive design creates an initial or replacement design for all the tables in the specified schemas. Create a comprehensive design when you are creating a new database.

To help the database designer create an efficient design, load representative data into the tables before you begin the design process. When you load data into a table, the database creates an unoptimized superprojection so that database designer has projections to optimize. If a table has no data, database designer cannot optimize it.

Optionally, supply database designer with representative queries that you plan to use so database designer can optimize the design for them. If you do not supply any queries, database designer creates a generic optimization of the superprojections that minimizes storage with no query-specific projections.

During a comprehensive design, database designer creates deployment scripts that:

• create projections to optimize query performance.
• create replacement buddy projections when database designer changes the encoding of existing projections that it decides to retain.

Incremental design

After you create and deploy a comprehensive database design, your database is likely to change over time. Consider using the database designer periodically to create incremental designs that address these changes. Changes that warrant an incremental design can include:

• Significant data additions or updates
• New or modified queries that you run regularly
• Performance issues with one or more queries
• Schema changes

View design details

The Designs tab lists the following information:

• Design name - The unique name of the design.
• Last updated on - The timestamp at which the design was last updated.
• Type - The type of the design.
• Status - The current status of the design. Valid values are:
  • Building - The status immediately after creation of the design.
  • Built - The status that is displayed after the design is successfully built.
  • Deployed - The status of the design when it is successfully deployed.
  • Error - The status that is displayed when there are errors in the design. Click the Details icon for more information.

In the Designs tab, you can view the design details.

1. Click the Details icon.

   The Projection statement, Log and Sample queries tabs are displayed.

2. Select Copy to copy the projection statement to the clipboard and analyze it in the SQL editor.

3. Select Download to download the projection statement to a file with .sql extension and analyze the projection statements outside the application.

4. Click Deploy to deploy the database design. The status of the database design changes to Deployed. The Database designer recommends projections for the database design. When you deploy the design, projections are created or dropped accordingly and displayed in the Projections tab of the OTCAD UI. These projections help improve query and data load performance.

5. Click the Log tab to view information about the events and the timestamp.

6. Click the Sample queries tab to view information about the SQL query and the query ID.

7. Click >> to close the projection details.

View projection details

The Projections tab lists the following information:

• Projection name - The name of the projection.
• Type - The method in which the projection was created. By default, projections of type DESIGNER that are created by the database designer are displayed.
• Table name - The name of the projection table.
• Schema - The name of the schema that contains the projection.
• Creator - The name of the projection's owner.
• Segmented - Specifies whether the projection is segmented.
• Up to date - Specifies whether projection data is up to date. Projections that are up to date are available to participate in query execution.

In the Projections tab, you can view the projection details.

1. Click the Details icon.

   The projection statement is listed.

2. Select Copy to copy the projection statement to the clipboard and analyze it in the SQL editor.

3. Click Download to download the projection statement to a file with .sql extension and analyze the projection statements outside the application.

4. Click >> to close the projection details.

Create a design

To create a design, do the following in the Database design page:

1. Select +Create a design.

2. In the Design name field, enter a unique name for the design. All designs must have unique names and should be no more than 32 characters long. Identifiers (names) of objects such as schema, table, projection, column names, and so on can be up to 128 bytes in length.

   Note

        Ensure that the design name complies with the design naming standard.
   

   The sample SQL queries are listed.

3. Do one of the following:

   • Choose Comprehensive.

     Note

        For optimal performance, it is recommended to restrict the number of queries to 400 if you choose this option.
     

     This option creates projections for all selected tables. Optional queries can improve optimization.
     • Choose one of the following options depending on your optimization objective:
       • Choose Optimize for performance - Designs that are optimized for query performance. These designs typically favor fast query execution over load optimization and thus result in a larger storage footprint.
       • Choose Optimize for load - Designs that are optimized for loads minimize database size, potentially at the expense of query performance.
       • Choose Balanced - Designs that are balanced between database size and query performance. <--or-->
     • Choose Incremental.
       

       Note

             For optimal performance, do not include more than 100 queries if you choose this option.
       

4. Click Proceed.

5. In the Select schema or table area, choose the schema. Expand a schema and choose the tables for inclusion in the database design.

6. Click Proceed.

7. In the What are the query samples area, provide the SQL queries. Do one of the following:

   • Choose Upload queries.
   • Click Browse and choose the file containing the SQL queries in your local drive that you need to upload. The upload summary is displayed with a count of queries in these states-Accepted, Illegal, Non-design, and Unsupported. Only queries that are in the Accepted state are considered for the database design. <--or-->
   • Choose Load from library. The sample SQL queries are listed.
   • Choose one of the options - Last 10, Last 50, or Last 100 to load the last 10, last 50, or last 100 SQL queries respectively.
   • From the Type list, choose one of the following options:
     • Most recent - The most recent SQL queries are displayed.
     • Longest running - The SQL queries that are running for the longest time duration are displayed.
     • Frequently executed - The SQL queries that are frequently executed are displayed.
     • Memory intensive queries - The SQL queries that use the maximum memory resources are displayed.
   • From the Duration list, choose one of the following options:
     • Last 1 hour
     • Last 24 hours
     • Last 7 days
     • Last 14 days
     • Custom
   • In the Actions column, choose the Details icon to view the details of the SQL query.
   • Select Copy to copy the SQL query to the clipboard and analyze it in the SQL editor.
   • Select Download to download the SQL query to a file with .sql extension.

8. Select Create. The database design is created and displayed in the Designs tab of the database designer page. Newly created database designs are displayed with a status Building.

Delete a design

You can delete a design that is no longer in use or required.

1. In the Database designer page, select the Designs tab.
2. Select ⋮ and Delete.
3. In the Confirmation dialog, select Delete.

This deletes the selected design and all it's configurations. The projections are not deleted.