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 MinMax 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 MinMax indexes on all columns.
Caution! Limiting MinMax 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 MinMax 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
Creates or does not create a sampled MinMax index, as described in MINMAX_SAMPLES option.
This option may not be combined with AUTO_TUNE or NOAUTO_TUNE.
AUTO_TUNE
Enables smart MinMax for this table and all columns are considered for inclusion into the smart MinMax index.
This option may not be combined with MINMAX_SAMPLES.
Setting a smart MinMax index enables Vector to dynamically choose a current subset of MinMax index columns based on collected statistics. This setting enables Vector to drop unnecessary index columns and add necessary columns to the MinMax index according to their predicted selectivity, with respect to upcoming filter queries, based on the set of filter queries seen so far.
Memory utilization is reduced while attempting to maintain run time performance, by eliminating index columns that are rarely queried or do not have a high (predicted) selectivity.
NOAUTO_TUNE = (column [{,column}])
Enables smart MinMax for this table and all columns, except for the specified columns, are considered for inclusion into the smart MinMax index.
This option may not be combined with MINMAX_SAMPLES.
MINMAX_SAMPLES Option
The WITH MINMAX_SAMPLES option produces a sampled MinMax index. A sampled MinMax index is used in conjunction with the automatic histogram generation in the optimizer. A sampled MinMax 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.