Neoview SQL Reference Manual (R2.4)

populate-option
When you create an index and do not specify POPULATE or NO POPULATE, POPULATE
is assumed. If you create an index using NO POPULATE, you must later request a POPULATE
INDEX command to actually load the data. See “POPULATE INDEX Utility” (page 218).
NO POPULATE
specifies that the index is not to be populated when it is created. The indexes are created,
but no data is written to the index, and it is marked offline. You can drop an offline
index with the DROP INDEX statement. The DROP TABLE statement also drops offline
indexes of the specified table. DML statements have no effect on offline indexes. If an
index is created with the intention of using it for a constraint, you must populate it before
creating the constraint. By using the POPULATE INDEX utility, you can populate an
offline index and remove its offline designation.
POPULATE
Specifies that the index is to be created and populated. If you omit the populate-option,
the default is POPULATE.
partn-file-option is:
{HASH PARTITION BY (partitioning-column, partitioning-column...)}
specifies the partitioning columns. If you do not specify the partitioning columns, the
default is the same partitioning column or columns as the base table for a non-unique
index, and all the columns in the index for a unique index.
Partitioning columns cannot be floating-point data columns.
Considerations for CREATE INDEX
When you create an index and do not specify POPULATE or NO POPULATE, POPULATE is
assumed. If you create an index using NO POPULATE, you must later request a POPULATE
INDEX command to actually load the data. See “POPULATE INDEX Utility” (page 218).
Authorization and Availability Requirements
To create a Neoview SQL index, you must be the must be the object (table or materialized view)
owner or the schema owner or have the ALTER or ALTER_TABLE privilege.
When the POPULATE option is specified, CREATE INDEX locks out INSERT, DELETE, and
UPDATE operations on the table being indexed. If other processes have rows in the table locked
when the operation begins, CREATE INDEX waits until its lock request is granted or timeout
occurs.
An index always has the same security as the table it indexes, so roles authorized to access the
table can also access the index. You cannot access an index directly.
Limits on Indexes
For nonunique indexes, the sum of the lengths of the columns in the index plus the sum of the
length of the clustering key of the underlying table cannot exceed 2048 bytes. For unique indexes,
the sum of the lengths of the columns in the index cannot exceed 2048 bytes.
There is no restriction on the number of indexes per table.
Example of CREATE INDEX
• This example creates an index on two columns of a table:
CREATE INDEX xempname
ON persnl.employee (last_name, first_name);
60 SQL Statements