CREATE TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE TABLE statement creates a base table.
Note: An Actian Data Platform 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.
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 Actian Data Platform 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 more information, see
WITH Clause.
Column Specification
The column specification in a CREATE TABLE statement defines the characteristics of a column in the table.
Note: For Actian Data Platform 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}]
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.
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), Actian Data Platform 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, Actian Data Platform 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/20',
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 Actian Data Platform 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, Actian Data Platform 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
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 DBMSs 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 USER … 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 Actian Data Platform 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 terminated. If the statement is within a multi-statement transaction, the transaction is not terminated.
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 more information, 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, Actian Data Platform 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.
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 an Ingres table.
PARTITION=(partitioning_scheme) | NOPARTITION
AUTOMATIC partitioning is the default for creating tables. However, if you want to specify how a table is to be partitioned, include a partitioning scheme. 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.
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 Actian Data Platform 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 the Actian Data Platform. Actian Data Platform is designed to operate efficiently, making the best use of cluster resources, when at least one table in any nontrivial (joining) query is partitioned. Lack of appropriate partitioning can result in sub-standard performance; partitioning, therefore, must be considered and implemented at the beginning of the evaluation.
Partitioning distributes the rows of a table among sub-tables (partitions). A partitioning scheme determines which rows are sent to which partitions.
Partitioning is managed automatically.
To define a table with an explicit partitioning scheme, use the PARTITION= option in the WITH clause of the CREATE TABLE statement. However, automatic partitioning is the default; WITH PARTITION need not be specified explicitly.
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. The partition type set may impact performance; for higher performance, tuning may be required.
The following distribution types are available:
• HASH – Distributes rows 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. HASH is data-dependent and requires the ON clause.
• AUTOMATIC – (Default) Randomly distributes rows among the partitions. For more information, see
Automatic Partitioning.
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).
The distribution scheme can be a default value set at the system level. For more information, see
Default Distribution Scheme.
Partitioned tables have the following restrictions:
• Only one dimension can be specified.
• The distribution type must be HASH or AUTOMATIC.
Partitioning Syntax
A table partition definition has the following format:
PARTITION = (dimension)
The syntax for each partition dimension is:
dimension = rule partitionspec
rule
Defines the type of distribution scheme for assigning rows to partitions. Valid values are:
HASH ON column{, column}
Distributes rows evenly among the partitions according to a hash value.
ON column{,column} specifies the columns to partition the table on.
AUTOMATIC
(Default) Distributes rows randomly among the partitions.
partitionspec
Defines the number of partitions and optionally their names:
partitionspec = DEFAULT PARTITIONS | [nn] PARTITION[S]
where:
DEFAULT PARTITIONS
Uses the default number of partitions configured for optimum performance based on your warehouse size. If you want to override this default, contact Actian Support.
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.
Guidelines for Partitioned Tables
Follow these guidelines when creating a partitioned table:
• 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.
• 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 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);
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 DEFAULT PARTITIONS);
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);
ALTER TABLE Y ADD CONSTRAINT fk_y FOREIGN KEY(d) REFERENCES X(a);
CREATE INDEX idx_y ON Y (d);
Automatic Partitioning
The default AUTOMATIC partitioning scheme randomly distributes rows evenly among the partitions. Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same partition. As a result, the system typically must reorganize the data before resolving the query. For example, joining two automatically partitioned tables usually requires reshuffling the rows. This extra step can degrade query performance.
AUTOMATIC partitioning should be used in the following cases:
• When insight is lacking for creating a good hash key. That is, when:
– There is no obvious joining key.
– There is no good candidate for hash distributing the table (arbitrary data).
– The table does not share a common join key with other tables.
– The table is a temporary staging table.
• When defining an automatically partitioned table as a preparatory step before creating a good hash key, you can use CREATE STATISTICS and then SELECT to get the minimum and maximum column values and COUNTs to better choose which columns to use as the HASH distribution key.
Automatic Partitioning Limitations
Automatic partitioning has the following limitations:
• AS UNIQUE and FOREIGN KEY constraint checks cannot be done locally for concurrent transactions with update operations on the same table. The first committed transaction wins, while the second triggers a constraint violation (when both of them contain update operations). The second transaction must be reissued.
• A partial MODIFY TO COMBINE operation may be slower due to extra network traffic.
• A full MODIFY TO COMBINE operation can redistribute tuples in the cluster.
Default Distribution Scheme
You can assign a default distribution scheme to be used when creating partitioned tables. The default distribution setting can be overridden at the session level using the SET PARTITION_SCHEME statement.
Default Partition Count
You can assign a default number of partitions to be used when creating partitioned tables. Specify WITH PARTITION = (rule ON column DEFAULT PARTITIONS) when creating or modifying tables. The table will be partitioned into the configured number of partitions.
The default partition count setting can be overridden at the session level using the SET PARTITION_PARTS statement.
CREATE TABLE Examples
1. For Google Cloud, create a table with default automatic partitioning:
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL)
2. Create a table without partitioning:
CREATE TABLE customer (
custid INT NOT NULL DEFAULT 0,
zip CHAR(5) NOT NULL)
WITH NOPARTITION;
3. Create a HASH-partitioned table with 16 partitions distributed based on emp_no column:
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);
4. Create a HASH-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 a table in which the Social Security number is encrypted using AES 128-bit encryption. Do not SALT the value (add 16 bytes of random bits to the field to further obfuscate the encrypted value):
CREATE TABLE socsectab (
fname CHAR(10),
lname CHAR(20),
socsec CHAR(11) ENCRYPT NOSALT )
WITH ENCRYPTION=AES128, PASSPHRASE='this is a secret', NOPARTITION;
6. 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;
7. 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;
8. 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);
9. Create a partitioned table with rows distributed automatically (that is, randomly):
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
PARTITION = (AUTOMATIC 8 PARTITIONS);
10. Create the “movies” table without partitioning:
CREATE TABLE movies AS SELECT * FROM cinema WITH NOPARTITION;
11. Create the “books” table with default AUTOMATIC partitioning:
CREATE TABLE books AS SELECT * FROM titles;