Neoview SQL Reference Manual (R2.3)

new table partitions do not inherit partition names from the original table. Instead,
Neoview SQL generates new names based on the physical file location.
If you specify the LIKE clause and the PARTITION file-option, you cannot specify
WITH PARTITIONS.
Considerations for CREATE TABLE
You can create partitioned and non-partitioned tables. To create a non-partitioned table, specify
the NO PARTITION option with the CREATE TABLE command.
Considerations for CREATE SET TABLE
A SET table is like any other normal table with the additional property of discarding duplicate
rows. A SET table does not generate duplicate row errors or warnings upon insert. Duplicate
row means an entire row inclusive of all columns. If the row being inserted has only identical
key columns, a duplicate key error is generated, much like normal table behavior.
The CREATE SET TABLE statement allows:
• Insert-select queries to continue processing without interruption in the event of duplicate
rows in the source table.
• Data loading tools to blindly load a table without worrying about duplicate rows in the
source data.
Restrictions
• CREATE SET TABLE is applicable only on base tables.
— not supported for index tables
— not supported for IDENTITY column inserts
— not applicable to tables with SYSKEY
• A row that contains default values for added columns cannot be treated as identical if the
inserting row contains the same default values in the corresponding columns.
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 table 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 maintained for volatile tables much like for persistent tables.
• 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 78).
Considerations for CREATE TABLE LIKE
The CREATE TABLE LIKE statement does not create views, owner information, or privileges
for the new table based on the source table. Privileges associated with a new table created by
using the LIKE specification are defined as if the new table is created explicitly by the current
user. If the source table has any unique or droppable primary key constraints, Neoview SQL
creates indexes for them on the target table. Other indexes on the source table are not created on
the target table.
74 SQL Statements