Neoview Database Administrator's Guide (R2.2)

Table 4-1 Notes for Creating Tables
NotesTable Option
• Name
• Data type
• Default value
• NULL/NOT NULL specification (NULL is the default value)
Columns
• You can change the sequence of the columns in the primary key. Although not
important from a logical primary key perspective, it is important from the
clustering key perspective.
• You should use DATE columns as leading columns of the primary key. For more
information about DATE columns, see the Neoview SQL Reference Manual.
• You can specify the order of the column: either ascending or descending.
Primary Key Columns
All constraints such as NULL and PRIMARY KEY are NOT DROPPABLE.Constraints
• Primary key columns serve as the default for the HASH BY columns.
• You can remove columns from the HASH BY list.
• The sequence of the columns in the HASH BY column list does not matter.
HASH BY Columns
DB Admin displays all the user roles, excluding the user ID creating the table. You
can grant privileges to multiple roles.
Grant Privileges
Partitioning and file attributes are set using default attributes, which are influenced
by the table type under creation. Partitioning for temporary tables is performed
through ODBC.
DB Admin provides an option for you to specify an approximate maximum table
size. It uses this value to decide the sizes of the primary and secondary extents. This
information also is used to decide on the maximum extents that will be used for
the table. You are prompted to indicate whether the table will be partitioned or
nonpartitioned. If nonpartitioned, DB Admin allocates a single partition for the
table. If partitioned, DB Admin allocates as many partitions to the table as qualifying
disks exist on the cluster.
Partitioning and File Attributes
• Ability to pick the index columns from a list of columns for the table
• Specification of the sequence of the columns within the list
• The order of the columns, either ascending or descending
• Specification that the index is unique
• HASH BY columns:
— If the index is unique, you must choose from the list of index columns. The
index defaults to all the index columns.
— If the index is not unique, you can choose from the list of index and primary
key columns. The index defaults to the HASH BY columns of the base table.
— The sequence of the columns in the HASH BY list does not matter.
• Partitioning and file attributes for the index are the same as for the base table
even though the extent requirements for an index might differ from the base
table.
• DB Admin creates indexes with the NO POPULATE option and immediately
schedules POPULATE INDEX and provides this feedback to you:
— The CREATE INDEX operation has been scheduled.
— After the CREATE INDEX operation completes, defragment the index.
— After the defragmentation is complete, run UPDATE STATISTICS on the
table.
Indexes
34 Planning Your Database