Assume the following primary-indexed source table definition for the following example set.
CREATE TABLE source_pi ( column_1 INTEGER column_2 INTEGER) UNIQUE PRIMARY INDEX (column_1);
Also assume you have created the following NoPI table definition for the example set:
CREATE TABLE source_nopi ( column_1 INTEGER column_2 INTEGER) NO PRIMARY INDEX;
The following CREATE TABLE … AS requests all produce a NoPI table because in each case you have explicitly specified NO PRIMARY INDEX:
CREATE TABLE target_nopi AS source_pi WITH DATA NO PRIMARY INDEX; CREATE TABLE target_nopi AS source_nopi WITH DATA NO PRIMARY INDEX; CREATE TABLE target_nopi AS ( SELECT column_1, column_2 FROM source_pi) WITH DATA NO PRIMARY INDEX; CREATE TABLE target_nopi AS ( SELECT column_1, column_2 FROM source_nopi) WITH DATA NO PRIMARY INDEX;
Unlike the preceding examples, the outcome of the following CREATE TABLE request depends on the setting of the DBS Control parameter PrimaryIndexDefault.
CREATE TABLE target AS ( SELECT column_1, column_2 FROM source_nopi) WITH DATA;
| IF the value for PrimaryIndexDefault is … | THEN the resulting table has … |
|---|---|
| N | no primary index. Even though you have not explicitly specified NO PRIMARY INDEX, you also have not specified any PRIMARY KEY or UNIQUE constraints on the columns of the table, so Vantage defines target as a NoPI table by default. |
| D or P | a nonunique primary index. The NUPI is defined by default, following the rules for selecting the primary index for a table when none is specified. The primary index is defined on the first column defined for target , which is column_1. The primary index is defined as a NUPI because no PRIMARY KEY or UNIQUE constraints are defined on any of the columns of target. |