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
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_statisticson global temp tables.
Using temp tables with
0weakens 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
0will 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: