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

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.

    • Using temp tables with KSAFE set to 0 may improve performance on your Data Warehouse instance.
    • 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.

    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: