Neoview SQL Reference Manual (R2.4)

Example for CREATE SET TABLE
In this example, the duplicate row is discarded.
>> CREATE SET TABLE t1(a LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
PRIMARY KEY, b INT);
--- SQL operation complete.
>>insert into t1 values (1,2);
--- 1 row(s) inserted.
>>insert into t1 values (1,2);
--- 0 row(s) inserted.
Considerations for CREATE VOLATILE TABLE
• Volatile temporary tables are very similar to persistent tables, except that the life span of
the table is limited to the session that created the table. Once the session ends, the tables are
automatically dropped.
• Volatile temporary tables are closely linked to the session. Their namespace is unique across
multiple concurrent sessions, and therefore allow multiple sessions to use the same volatile
temporary table names simultaneously without any conflicts.
• Volatile tables support creation of indexes.
• Volatile tables are partitioned by the system.
• Statistics are not automatically updated for volatile tables. If you need statistics, you must
explicitly run UPDATE STATISTICS.
• Volatile tables can be created and accessed using one-part name or two-part names. However,
you must use the same name (one part or two part) for any further DDL or DML statements
on the created volatile table. See “Examples of CREATE TABLE” (page 89).
• Neoview SQL allows users to explicitly specify primary key, STORE BY, and HASH
PARTITION BY clauses on columns that contain null values.
• Neoview SQL does not require that the first column in a volatile table contain not null values
and be the primary key. Instead, Neoview SQL attempts to partition the table, if possible,
using an appropriate suitable key column as the primary and partitioning key. For more
information, see “How Neoview SQL Selects Suitable Keys for Volatile Tables” (page 78).
• When creating a volatile table with the AS SELECT option, it is recommended that you first
CREATE VOLATILE SCHEMA in a separate transaction before creating the volatile table.
This action avoids catalog locking issues.
Restrictions for CREATE VOLATILE TABLE
These items are not supported for volatile tables:
• ALTER statement
• User constraints
• Creating views, triggers, materialized views
• Creating non-volatile indexes on a volatile table or a volatile index on a non-volatile table
• CREATE TABLE LIKE operations
• Utility operations, such as MAINTAIN
How Neoview SQL Supports Nullable Keys for Volatile Tables
• Allows nullable keys in primary key, HASH PARTITION BY, STORE BY, and unique
constraints.
• A null value is treated as the highest value for that column.
• A null value as equal to other null values and only one value is allowed for that column.
CREATE TABLE Statement 77