Logical Schema Design - Tables and Views

You can create the logical database schema with a standard CREATE TABLE command. Similarly, you create views using the CREATE VIEW command.

A Data Warehouse view is just a persisted SELECT statement. There is no significant performance difference between querying a derived result set inlined as a sub-select versus persisted as view.

Materialized views are supported only for BigQuery and PostgreSQL Data Sources.

You can use the following types of tables:

• Standard (partitioned) tables For more information about partitioned tables, see Take Advantage of Table Partitions and the Vertica online documentation on using table partition.

• Flex (flexible) tables  Flex tables are used for loading and querying unstructured data. Before you start working with flex tables, we recommend that you review Flex Table Limits. For detailed information about flex tables and how to use them, see Vertica online documentation on using flex tables.

• Temporary (temp) tables  Temp tables keep data only during the current session. Use temp tables when you need to store data only for a single session, instead of performing DROP and then CREATE for persistent tables.

  • If you want data in a temp table to survive COMMIT, use ON COMMIT PRESERVE ROWS.

  • If you want a table definition (not data!) to persist, use GLOBAL TEMP TABLE. Be aware that you cannot use analyze_statistics on global temp tables.

  • Using temp tables with KSAFE set to 0 weakens the resiliency of your data, but may improve performance on your Data Warehouse instance. If a node of the ADS cluster goes down, any local temporary tables with KSAFE set to 0 will be unavailable. Only use this option if you understand and accept the risk.

  For detailed information about temp tables and how to use them, see Vertica online documentation on using temp tables.

Select from the following topics to get more information:

• Primary and Foreign Keys
• Altering Logical Schema