WITH Clause
The WITH clause begins with the keyword WITH followed by any of the following options, each separated by a comma:
STRUCTURE=
Defines the storage structure of the table. Valid structures are:
X100
(Default) Stores data in columns. Puts as few columns as possible in one disk block. An alias for X100 is VECTORWISE.
X100_ROW
Stores data in rows. Puts as many columns as possible in one disk block. An alias for X00_ROW is VECTORWISE_ROW.
HEAP
Explicitly declares the structure to be a traditional Ingres table.
Note: You must be licensed to use HEAP tables in Vector.
LOCATION=location_spec
Specifies the location where the new table is created. The location_name must exist (created with the CREATE LOCATION statement) and the database must have been extended to the corresponding area.
Table location and column location cannot be specified in the same statement. All specified locations must be default or DATA locations.
If the location option is omitted, the table is created in the default database location.
For details about defining location names and extending databases, see the Vector User Guide.
LOCATION=location_spec can be one of the following syntax options:
LOCATION=(location_name {, location_name} )
Creates all columns in the specified locations, horizontally partitioned. If one location is specified, creates all columns in the specified location.
LOCATION(column_name)=(location_name) [, LOCATION(column_name)=(location_name)]
Creates each column in the specified location (vertically partitioned). The column named “default” must be escaped.
LOCATION(column_name)=(location_name1 , location_name2)
Creates the column in horizontal partitions in the specified locations. The column named “default” must be escaped.
LOCATION(DEFAULT)=(location_name {, location_name})
Specifies the locations for columns defined in CREATE TABLE but not explicitly listed in WITH LOCATION. Creates columns in the specified locations, horizontally partitioned.
Note: The LOCATION(DEFAULT) syntax does not define “default” locations for the table.
JOURNALING
Explicitly enables or disables journaling (incremental backup) on the table.
To set the session default for journaling, use the SET [NO]JOURNALING statement. The session default specifies the setting for tables created during the current session. To override the session default, specify the WITH [NO]JOURNALIING clause in the CREATE TABLE statement.
If journaling is enabled for the database and a table is created with journaling enabled, journaling begins immediately. If journaling is not enabled for the database and a table is created with journaling enabled, journaling begins when journaling is enabled for the entire database.
Note: To enable or disable journaling for the database and for system catalogs, use the ckpdb command.
PARTITION=(partitioning_scheme) | [NOPARTITION]
SECURITY_AUDIT = (table_audit_opt {, table_audit_opt})
Specifies the level of security auditing, as follows:
TABLE
(Default) Implements table-level security auditing on general operations (for example create, drop, modify, insert, or delete) performed on the table.
[NO]ROW
Not supported for Vector tables. Implements row-level security auditing on operations performed on individual rows, such as insert, delete, update, or select.
[NO]MINMAX = [(column {, column})]
Creates or does not create min-max indexes on all or specified columns.
WITH [NO]MINMAX affects all columns. WITH [NO]MINMAX=(column, column) affects the listed columns and sets the other columns to the opposite.
The default behavior is to create min-max indexes on all columns.
Caution! Limiting min-max indexing with NOMINMAX can severely impact performance. Use NOMINMAX only if necessary to alleviate memory usage problems with very wide tables (many columns), and then use it only for columns not involved in restrictions or joining, if possible.
Note: After a min-max index has been created for a table or column, the column_has_minmax column in the iicolumns catalog will have a value of Y (yes).
[NO]MINMAX_SAMPLES
MINMAX_SAMPLES Option
The WITH MINMAX_SAMPLES option produces a sampled min-max index. A sampled min-max index is used in conjunction with the automatic histogram generation in the optimizer. A sampled min-max index aids the execution engine like a non-sampled one does, but also provides an automatic histogram much faster than a full scan-generated histogram, thus letting the optimizer produce better query plans faster.
The WITH [NO]MINMAX_SAMPLES option can be used on the CREATE TABLE [AS SELECT], DECLARE GLOBAL TEMPORARY TABLE [AS SELECT], and ALTER TABLE...ADD MINMAX statements.
MINMAX_SAMPLES is a table property; it sets the default sampling state for the table. The default is NOMINMAX_SAMPLES.
To retrieve the current MINMAX_SAMPLES setting (valid values are Y and N), connect to the database and issue the statement:
SELECT MINMAX_SAMPLES FROM iitables WHERE table_name = ‘your_table’;
To change the MINMAX_SAMPLES property of a table, use ALTER TABLE... ADD MINMAX column-list SET [NO]MINMAX_SAMPLES. The setting you specify becomes the new permanent MINMAX_SAMPLES property of the table.
Note: You cannot alter an existing min-max index to be sampled or not-sampled; you must drop and re-add it.