CREATE TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE TABLE statement creates a base table.
Note: A Vector table cannot be created in either iidbdb or imadb (system databases).
This statement has the following format:
CREATE TABLE [IF NOT EXISTS] [schema.] table_name
(column_specification {, column_specification })
[table_constraint {, table_constraint}]
[with_clause]
The CREATE TABLE...AS SELECT statement (which creates a table and loads rows from another table) has the following format:
CREATE TABLE table_name
(column_name {, column_name}) AS
[WITH common_table_expression] subselect
{UNION [ALL]
subselect}
[with_clause]
where:
IF NOT EXISTS
Creates the table if it does not exist and returns without error if the table already exists.
Note: Use this option with caution. The table definition of a pre-existing table may differ with that of the CREATE TABLE IF NOT EXISTS statement.
table_name
Defines the name of the new table. It must be a valid object name.
column_specification
Note: For Vector tables, defaults, nullability, and identity columns work only for INSERT...VALUE, not INSERT SELECTs.
table_constraint
with_clause
Specifies WITH clause options separated by a comma. For details, see
WITH Clause.
IMPORTANT! The WITH PARTITION= or WITH NOPARTITION clause is required. For details, see
Partitioned Tables.
Column Specification
The column specification in a CREATE TABLE statement defines the characteristics of a column in the table.
Note: For Vector tables, defaults, nullability, and identity columns work only for INSERT...VALUE, not INSERT SELECTs.
The column_specification has the following format:
column_name datatype
[[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT] [WITH NULL | NOT NULL]
[MASKED [AS {BASIC | NULL | 0 | ' ' }]
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
[[CONSTRAINT constraint_name] column_constraint
{ [CONSTRAINT constraint_name] column_constraint}]
[ENCRYPT [SALT | NOSALT]]
column_name
datatype
Assigns a valid data type to the column. If CREATE TABLE...AS SELECT is specified, the new table takes its column names and formats from the results of the SELECT clause of the subselect specified in the AS clause (unless different column names are specified).
Note: For char and varchar columns, the column specification is in number of bytes (not number of characters).
DEFAULT clause
Specifies whether the column is mandatory.
WITH NULL | NOT NULL
Specifies whether the column accept nulls:
WITH NULL
(Default) Indicates that the column accepts nulls. If no value is supplied by the user, null is inserted.
NOT NULL
Indicates that the column does not accept nulls.
[MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }]
Displays the column with the specified mask characteristic unless the user has the UNMASK privilege. The mask characteristic defines how to display the masked data:
BASIC – Fills the width of the column with asterisks
NULL – NULL
0 – 0
‘ ‘ – blank
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)] | GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates the column is an
IDENTITY Columns. The column must be defined as integer or bigint.
[CONSTRAINT constraint_name] column_constraint
Specifies checks to be performed on the contents of the column to ensure appropriate data values.
ENCRYPT [SALT | NOSALT]
Specifies that the column values be encrypted. ENCRYPT must be used with the WITH ENCRYPTION option, as described in
WITH Clause.
All data types can be encrypted. An encrypted column cannot be part of a table key.
By default, encrypted columns use salt, which is 16 bytes of random bits added to the encrypted column to further obfuscate the value. For reasons to specify NOSALT, see Understanding SALT in the Security Guide.
DEFAULT Clause
The WITH|NOT DEFAULT clause in the column specification specifies whether a column requires an entry.
This clause has the following format:
[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT
[WITH] DEFAULT default_spec
Indicates that if no value is provided (because none is required), the DBMS Server inserts the default value. The default value must be compatible with the data type of the column.
For character columns, valid default values include the constants: USER, CURRENT_USER, and SYSTEM_USER.
For boolean columns, valid default values include FALSE or TRUE.
WITH DEFAULT
Indicates that if no value is provided, the DBMS Server inserts 0 for numeric and money columns, an empty string for character columns, the current date for ANSI date columns, and the current timestamp for timestamp columns.
NOT DEFAULT
Indicates the column is mandatory (requires an entry).
The following is an example of using the DEFAULT clause:
CREATE TABLE DEPT(dname CHAR(10),
location CHAR(10) DEFAULT 'NY',
creation DATE DEFAULT '01/01/12',
budget MONEY DEFAULT 10000);
Restrictions on the Default Value for a Column
The following considerations and restrictions apply when specifying a default value for a column:
• The data type and length of the default value must not conflict with the data type and length of the column.
• The maximum length for a default value is 1500 characters.
• For fixed-length string columns, if the column is wider than the default value, the default value is padded with blanks to the declared width of the column.
• For numeric columns that accept fractional values (floating point and decimal), the decimal point character specified for the default value must match the decimal point character in effect when the value is inserted. To specify the decimal point character, set II_DECIMAL.
• For money columns, the default value can be exact numeric (integer or decimal), approximate numeric (floating point), or a string specifying a valid money value. The decimal point and currency sign characters specified in the default value must match those in effect when the value is inserted.
• For date columns, the default value must be a string representing a valid date. If the time zone is omitted, the time zone defaults to the time zone of the user inserting the row.
Masked Column Default Values
For masked columns, the default value for the BASIC, NULL, 0, and ‘ ‘ mask characteristics by data type are as follows:
Note: In the following table, N/A means that trying to mask the column with that characteristic returns an error. For character types, the number of asterisks in the mask matches the defined size of the data type.
IDENTITY Columns
Note: Sequences, including identity columns, work for Vector tables only in specific circumstances; we do not recommend the use of sequences or identity columns except in consultation with Actian Support or Actian Services.
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence.
An identity column is a way to automatically generate a unique numeric value for each row in a table. A table can have only one column that is defined with the identity attribute.
The IDENTITY clause has the following format:
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
where:
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
Indicates that the column value is determined by the corresponding sequence. The user cannot specify an explicit value for the column in an INSERT or UPDATE statement.
INSERT statements that contain ALWAYS identity columns in their column list must specify DEFAULT as the corresponding value. To override this behavior, use the OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE clauses of the INSERT statement.
The data type of the sequence matches the data type of the identity column.
GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates that the user can optionally provide an explicit value for the column.
seq_name
Defines the name of the sequence.
seq_options
Control how the sequence supplies data when requested by an application. Sequence options can be specified in any order, and none are required.
Any of the following seq_options can be specified in a blank-space separated list:
START WITH number
Specifies the start of the sequence as an integer constant. The default value is 1 for positive sequences (positive increment) and -1 for negative sequences (negative increment). (This option is valid with the CREATE SEQUENCE statement only.)
RESTART WITH number
Specifies a new start value for the sequence. (This option is valid with the ALTER SEQUENCE statement only.)
INCREMENT BY number
Specifies the increment value (positive or negative) that produces successive values of the sequence.
Default: 1
MAXVALUE number
Specifies the maximum value allowed for the sequence.
Defaults:
For positive integer sequences: 2**31-1
For positive bigint sequences: 2**63-1
For positive decimal(n) sequences: 10**(n+1)-1
For negative sequences: -1
NO MAXVALUE / NOMAXVALUE
Specifies that sequences can generate values with an upper bound equivalent to that of the data type chosen to hold the sequence (for example, 2**31-1 for integers).
MINVALUE number
Specifies the minimum value allowed for the sequence.
Default:
For positive sequences: 1
For negative bigint sequences: -2**63
For negative integer sequences: -2**31
For negative decimal(n) sequences: -(10**(n+1)-1)
NO MINVALUE / NOMINVALUE
Specifies that sequences can generate values with a lower bound equivalent to that of the data type chosen to hold the sequence (for example, -2**31 for integers).
CACHE number
Specifies the number of sequence values held in server memory. When the supply of numbers is exhausted, Vector requires a catalog access to acquire the next set.
Default: 20
NO CACHE / NOCACHE
Specifies that sequence values are not to be cached by the server. When this option is selected, a catalog access is required for each request for a sequence value. This can severely degrade application performance.
Default: CACHE 20 (when neither CACHE nor NOCACHE are specified), which ensures low catalog overhead.
Note: Gaps between two sequence values may occur: If the DBMS is restarted between two requests of a sequence value, the cached but not used values are lost and the cached values are not written back when shutting down. After restart, the Sequence Cache is empty, so the next value is taken from iisequence.
Note: In a multiple DBMS shared cache environment, sequence values may not be in chronological order. Each DBMS maintains its own Sequence Cache. No value appears twice, but the order of the requested values depends on which DBMS is returning the sequence value.
CYCLE
Specifies that the sequence restarts at the beginning value once it reaches the minimum value (negative increment) or maximum value (positive increment).
Default: NO CYCLE
NO CYCLE / NOCYCLE
Specifies that the sequence is not cycled when the last valid value is generated. An error is issued to the requesting transaction.
ORDER
NO ORDER / NOORDER
These options are included solely for syntax compatibility with other DBMSes that implement sequences, and are not currently supported.
Default: NOORDER
SEQUENTIAL / UNORDERED
Specifies whether values are returned sequentially or in unordered sequence. RANDOM is a synonym for UNORDERED. This option is ignored for decimal-based sequences.
Default: SEQUENTIAL
The sequence created to manage identity column values is accessible by its generated name. The generated sequence, however, cannot be explicitly dropped; instead, the identity column or table must be dropped, or the ALTER TABLE … ALTER COLUMN … DROP IDENTITY statement must be used.
For example:
GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 20)
Note: When using the COMBINE command on a table with an identity column unexpected results, such as duplicate values, may occur. When records are inserted using COMBINE rather than the SQL INSERT, the values in the identify column in the Vector table will not be synchronized with the identity values stored in the system catalogs.
Constraints
To ensure that the contents of columns fulfill your database requirements, specify constraints.
Ordinary (enforced) constraints are always checked at the end of every statement that modifies the table. If the constraint is violated, an error is returned and the statement is aborted. If the statement is within a multi-statement transaction, the transaction is not aborted.
Constraints can also be declared as NOT ENFORCED. This allows the database designer to describe a constraint (such as a referential relationship) without the overhead of checking the constraint. The assumption is that the constraint will be enforced externally in some way, and so the DBMS does not have to do it. The constraint description is available to the query optimizer and to external query generators, allowing better query plans or better queries to be generated. If the actual data violates the non-enforced constraint, incorrect results are possible.
Constraints can be specified for individual columns or for the entire table. For details, see
Table-level and Column-level Constraints.
The types of constraints are:
• Unique constraint–Ensures that a value appears in a column only once. Unique constraints are specified using the UNIQUE option.
• Referential constraint–Ensures that a value assigned to a column appears in a corresponding column in another table. Referential constraints are specified using the REFERENCES option.
• Primary key constraint–Declares one or more columns for use in referential constraints in other tables. Primary keys must be unique.
Table-level and Column-level Constraints
Constraints can be specified for groups of columns as part of the table definition (table-level constraints) or for individual columns as part of the column specification (column-level constraints).
The constraint has the following syntax:
[CONSTRAINT constraint_name] constraint
constraint_name
Defines a name for the constraint. If the name is omitted, the DBMS Server assigns one. The constraint name is used when dropping the constraint using the ALTER TABLE statement.
Note: We recommend defining a name when creating a constraint; otherwise system catalogs must be queried to determine the system-defined name.
constraint
Is either a table-level constraint (table_constraint) or a column-level constraint (column_constraint).
table_constraint is one or more of the following:
UNIQUE (column_name {, column_name})
PRIMARY KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})] [enforce_option] [referential_actions]
column_constraint is one or more of the following:
UNIQUE
PRIMARY KEY
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name[(column_name)] [enforce_option] [referential_actions]
where:
enforce_option
Specifies whether constraints are enforced. Valid values are:
NOT ENFORCED
Does not enforce the constraint when it is defined or when the table is updated. The constraint is defined in the database catalogs. NOT ENFORCED constraints can be used to generate improved SQL statements or query plans.
Indexes are not created for NOT ENFORCED constraints.
ENFORCED
(Default) Enforces the constraint.
referential_actions
Defines alternate processing options if a referenced row is deleted or if referenced columns are updated when there are existing matching rows. A referential action specifies either an update rule or a delete rule, or both, in either sequence.
The ON UPDATE and ON DELETE rules have the following syntax:
ON UPDATE {RESTRICT | NO ACTION}
or
ON DELETE {RESTRICT | NO ACTION}
• NO ACTION - (Default) Returns an error upon any attempt to delete or update a referenced row with matching referencing rows.
• RESTRICT - Behaves the same as NO ACTION, but returns a different error code.
Examples
Here is an example of column-level constraints:
CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
location CHAR(10) UNIQUE;
Note: Multiple column constraints are separated by a space.
Here is an example of table-level constraints:
CREATE TABLE yourtable(firstname CHAR(20) NOT NULL,
lastname CHAR(20) NOT NULL,
UNIQUE(firstname, lastname));
WITH Clause
The WITH clause begins with the keyword WITH followed by any of the following options, each separated by a comma.
Note: The only required WITH clause option is the PARTITION (or NOPARTITION) option.
STRUCTURE=
Defines the storage structure of the table. Valid structures are:
VECTORWISE
(Default) Stores data in columns. Puts as few columns as possible in one disk block.
VECTORWISE_ROW
Stores data in rows. Puts as many columns as possible in one disk block.
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 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.
PARTITION=(partitioning_scheme) | NOPARTITION
(Required) Specifies how a table is to be partitioned. If the table is not to be partitioned, you must specify NOPARTITION.
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.
ENCRYPTION=encryption type, [AESKEY=hex-aes-key,] PASSPHRASE='encryption-passphrase'
Specifies encryption options to secure data in a column defined with the ENCRYPT option:
encryption type
Specifies the type of Advanced Encryption Standard (AES) encryption for the column:
AES128
128-bit encryption
AES192
192-bit encryption
AES256
256-bit encryption
AESKEY=
Specifies the internal key used to encrypt the user data. The key is protected by a key derived from the PASSPHRASE. The AESKEY must be the exact hex value of the key, which depends on whether AES128, AES192, or AES156 is specified (thus 16, 24, or 32 bytes) and is in the usual Ingres hex constant format of x'03010401050902060503050901040104' or 0x03010401050902060503050901040104.
If no AESKEY is specified, a random key is generated.
PASSPHRASE='encryption passphrase'
Specifies the encryption passphrase used to encrypt and decrypt the column data. The passphrase must be at least eight characters, can contain spaces, and must be specified within single quotation marks (' '). The string is converted to the appropriate AES key size (16, 24, or 32 bytes).
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.
Partitioned Tables
Partitioning is an essential strategy in VectorH.
When the configuration parameter partition_spec_required=vector is set in config.dat, the system requires you to specify either the WITH PARTITION=(...) or WITH NOPARTITION clause when creating a Vector table using CREATE TABLE or CREATE TABLE AS SELECT syntax. This configuration setting is the default.
IMPORTANT! In a query involving two or more tables, at least one table must be partitioned.
Partitioning distributes the rows of a table among sub-tables (partitions). A partitioning scheme determines which rows are sent to which partitions.
After the partitioning scheme is defined, partitioning is managed automatically.
To define a table with partitions, use the PARTITION= option in the WITH clause of the CREATE TABLE statement.
Partitioned tables have the following restrictions:
• Only one dimension can be specified.
• The distribution type must be HASH.
• Only one location per partition is allowed.
Partitioning Schemes
Each dimension of a partitioning scheme defines a rule (distribution scheme) for assigning rows to partitions. Conceptually, a dimension defines a set of logical partitions. Only one dimension is allowed.
One distribution type is available: HASH. Hash is data-dependent and requires the ON clause.
A hash distribution spreads row evenly among the partitions by use of a hash value (instead of randomly). Given a value for the partitioning columns, a query can predict which partition contains the rows that have the matching value. Thus, a query can restrict its search to a subset of partitions.
The optional logical partition names must be unique for each table. The same partition name can occur in other partitioned tables. If a partition name is omitted, the system generates a name (of the form iipartnn).
If NO LOCATION= is specified for a partition, the location list is defined by the enclosing statement's with_clause (that is, the with_clause that contains the PARTITION= clause).
Partitioning Syntax
A table partition definition has the following format:
PARTITION = (dimension)
The syntax for each partition dimension is:
dimension = rule ON column {, column }
partitionspec {, partitionspec}
| rule partitionspec {, partitionspec}
rule
Defines the type of distribution scheme for assigning rows to partitions. The only valid value is:
HASH
Distributes rows evenly among the partitions according to a hash value.
ON column {, column}
Specifies the columns to partition the table on.
partitionspec
When rule is HASH, defines the number of partitions and optionally their names:
partitionspec = DEFAULT PARTITIONS | [nn] PARTITION[S] [ ( name {, name} ) ] [with_clause]
where:
DEFAULT PARTITIONS
Uses the number of partitions specified in the DBMS configuration parameter default_npartitions. The statement returns an error if the default partition value is not set. For more information, see
Default Partition Count. If DEFAULT PARTITIONS is specified, neither explicit partition names nor a with_clause can be specified.
nn
Is the number of partitions, which defaults to 1 if omitted.
name
Identifies the partition. When the number of partitions is two or more, a comma-separated list of names can be provided to override the default value.
Default: iipartNN
with_clause
Specifies WITH clause options for partitioning.
The with_clause for partitioning has the following format:
WITH with-option
where
with-option = LOCATION = ( location )
When specifying a LOCATION for the table, this location will only be used for PARTITIONS that are lacking a WITH LOCATION clause.
Guidelines for Partitioned Tables
Follow these guidelines when creating a partitioned table:
• The locations for partitions should be different to exploit the I/O performance of different devices.
• The partition key should be chosen from columns that have uniform values, for example, primary/foreign keys.
• When you expect to have many queries that join tables A and B on the condition A.fk_col = B.col, good partitioning keys for A and B are fk_col and col, respectively.
• When you expect to join tables A and B often, the locations specified for tables A and B should be the same.
• We recommend at most one partition per core, per node:
num_partitions = num_nodes * K
where K is a divisor of number of physical cores per node.
• Creating an index on the columns that are used to define a foreign relationship is not allowed when the tables joined by the foreign relationship do not have the same number of partitions or are not partitioned on the columns (or a matching subset) used for the foreign key relationship. For example:
The following is allowed:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL,
c i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a,c 2 PARTITIONS);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a,c);
CREATE TABLE Y (c i4 NOT NULL,
d i4 NOT NULL,
e i4)
WITH PARTITION=(HASH ON d,e 2 PARTITIONS WITH LOCATION=(db_loc1));
ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d,e) REFERENCES X(a,c);
CREATE INDEX idx_y ON Y(d,e);
Partitioning keys on c for X and e for Y is also valid.
The following is not allowed:
CREATE TABLE X (a i4 not null,
b i4 not null)
WITH
PARTITION=(HASH on a 2 PARTITIONS,
1 PARTITION WITH LOCATION=(db_loc2),
1 PARTITION);
ALTER TABLE X ADD CONSTRAINT pk_x PRIMARY KEY (a);
CREATE TABLE Y (c i4 NOT NULL,
d i4 NOT NULL)
WITH PARTITION=(HASH ON d 2 PARTITIONS WITH LOCATION=(db_loc1));
ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d) REFERENCES X(a);
CREATE INDEX idx_y ON Y (d);
Default Partition Count
You can assign a default number of partitions to be used when creating partitioned tables. Set the default value on the DBMS configuration parameter default_npartitions, and then specify WITH PARTITION = (HASH ON column DEFAULT PARTITIONS) when creating or modifying tables. The table will be partitioned into the configured number of partitions. The default_npartitions parameter can be set to an integer between 0 and 923, inclusive.
For preconfigured cloud environments, this default is set automatically according to cluster topology. For VectorH on premises (does not apply to Avalanche), the default is 0 (no default available); use partitioning guidelines to set default_npartitions.
CREATE TABLE Examples
1. Create a table in the default HDFS location (II_HDFSDATA).
Note: Examples 1 and 2 assume the database was created in the default HDFS location (for example: createdb -vii_hdfsdata customer).
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL)
WITH NOPARTITION;
2. Create a table in the default HDFS location with 8 partitions distributed across the location:
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL
) WITH PARTITION = (HASH ON custid 8 PARTITIONS);
3. Create a partitioned table with rows distributed evenly based on a hash value:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no
16 PARTITIONS WITH LOCATION = (location1));
4. Create a partitioned table using the default partition count:
CREATE TABLE employee (
emp_no INTEGER NOT NULL NOT DEFAULT,
emp_name CHAR(32) NOT NULL NOT DEFAULT,
dept_no INTEGER,
emp_rating INTEGER)
WITH JOURNALING,
PARTITION = (HASH ON emp_no DEFAULT PARTITIONS);
5. Create two partitions in db_loc1:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a 2 PARTITIONS);
6. Create two partitions with location db_loc2. Location db_loc1 will be ignored:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a 2 PARTITIONS WITH LOCATION=(db_loc2));
7. Create four partitions, two in location db_loc2, one in location db_loc1, and one in location db_loc3:
CREATE TABLE X (a i4 NOT NULL,
b i4 NOT NULL)
WITH LOCATION=(db_loc1),
PARTITION=(HASH ON a 2 PARTITIONS WITH LOCATION=(db_loc2),
1 PARTITION,
1 PARTITION WITH LOCATION=(db_loc3));
8. Add a new HDFS location to an existing database and create a table with no logical partitions that uses default HDFS replication based on that location.
CREATE LOCATION vhl01 WITH AREA = 'hdfs://namenode:54310/vectorwise/vhl01', USAGE = (DATABASE);
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL
) WITH LOCATION = (vhl01), NOPARTITION;
9. Create a table in which the social security number is encrypted using AES 128-bit encryption. Do not add 16 bytes of random bits to the field to further obfuscate the encrypted value (NOSALT):
CREATE TABLE socsectab (
fname CHAR(10),
lname CHAR(20),
socsec CHAR(11) ENCRYPT NOSALT )
WITH ENCRYPTION=AES128, PASSPHRASE='this is a secret', NOPARTITION;
10. Create a table in which data for column c2, which contains salary data, is encrypted using AES 256-bit encryption. Salt is added to the field by default:
CREATE TABLE t1 (
c1 CHAR(20) NOT NULL,
c2 MONEY ENCRYPT)
WITH ENCRYPTION=AES256, PASSPHRASE='decoder ring', NOPARTITION;
11. Create a table with a sampled min-max index on two columns:
CREATE TABLE sales_fact (
sales_date ANSIDATE,
value INTEGER2,
quantity FLOAT8)
WITH MINMAX=(sales_date, quantity), MINMAX_SAMPLES;
12. Create a table with the address and salary columns masked:
CREATE TABLE employee(
name VARCHAR(20),
address VARCHAR(20) MASKED,
salary FLOAT MASKED AS 0);