The primary index definition.
The primary index is used by the hashing algorithm to distribute table rows across the AMPs.
Definitions that do not explicitly specify PRIMARY INDEX (column_list) or NO PRIMARY INDEX, the system uses the setting of the DBS Control parameter PrimaryIndexDefault, except for column-partitioned tables, which are always defined by default with NO PRIMARY INDEX regardless of the PrimaryIndexDefault setting. For information about DBS Control parameters, see Utilities, B035-1102.
For column-partitioned tables, the default table kind is MULTISET. This cannot be changed for a column-partitioned table.
For information about primary index defaults, see Database Design, B035-1094.
- A column in the column set that defines a partitioned primary index.
- If you specify more than one column name, the index is created on the combined values of each column named. A maximum of 64 columns can be specified for an index, and a maximum of 32 secondary indexes can be created for one table. A multicolumn NUSI defined with an ORDER BY clause counts as two consecutive indexes in this calculation.
- You can define a primary index on a column that has a UDT data type.
- You cannot define a primary index on a column defined with any of the following data types:
- LOB UDT
- You cannot define a primary index on a column that is a row-level security constraint.
- Optional name for the index.
- For information about naming database objects, see SQL Fundamentals, B035-1141.
- The primary index is to be unique.
- Any secondary indexes and the primary index can be defined to be unique with the exception of a PI whose definition does not include all of the partitioning columns, if any.
Example: Specifying a UPI and a USI on the Same Table
In this example, a unique primary index is defined on the empno column, and a unique secondary index is defined on the name column. Because the empno and name columns must always contain a value, they are assigned the NOT NULL attribute.
For display purposes, the values for sex , race , and mstat are defined using the UPPERCASE column attribute. The name column is not defined as a unique index because it is possible for two or more employees to have the same name.
CREATE TABLE employee ( emp_no SMALLINT FORMAT '9(5)' CHECK (emp_no >= 10001 AND emp_no <= 32001) NOT NULL, name VARCHAR(12) NOT NULL, dept_no SMALLINT FORMAT '999' CHECK (deptno >= 100 AND dept_no <= 900), job_title VARCHAR(12), salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' CHECK (salary >= 1.00 AND salary <= 999000.00), yrs_exp BYTEINT FORMAT 'Z9' CHECK(yrs_exp >= -99 AND yrs_exp <=99), dob DATE FORMAT 'MMMbDDbYYYY' NOT NULL, sex CHARACTER UPPERCASE NOT NULL CHECK (sex IN ('M','F')), race CHARACTER UPPERCASE, m_stat CHARACTER UPPERCASE CHECK (m_stat IN ('S','M','D','U')), edlev BYTEINT FORMAT 'Z9' CHECK (ed_lev >=0 AND ed_lev <=22) NOT NULL, h_cap BYTEINT FORMAT 'Z9' CHECK (h_cap >= -99 AND h_cap <= 99) UNIQUE PRIMARY INDEX (emp_no), UNIQUE INDEX (name);
Example: Specifying an Identity Column as a Unique Primary Index
This example creates an identity column that is used as the primary index for the table. Values for idnum are generated starting with 1,000 and incrementing by 10 until the specified MAXVALUE of 300,000 is reached.
The numbers generated for rows inserted into the table are the following: 1,000, 1,010, 1,020, …, 300,000 and then an error is returned when the maximum value is reached.
CREATE TABLE t ( idnum INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10 MINVALUE 0 MAXVALUE 300000), phone INTEGER) UNIQUE PRIMARY INDEX(idnum);
This example does not specify a CYCLE parameter.
|Not specified.||Specification defaults to NO CYCLE.|
|Specified.||Numbering sequence generated is as follows: 1,000, 1,010, 1,020 … 300,000, 0, 10, 20, 30 …|
No warning message is returned when numbers are cycled. However, if an idnum value is still in existence when an attempt is made to recycle it, the request aborts because idnum is the UPI for the table, so all its values must be unique.
If there is a system restart, the numbering sequence might not be in strict increments of 10 from the last generated number. The next available number stored in DBC.IdCol.AvailValue for that particular table is retrieved and numbering begins from there. Unassigned numbers last reserved and stored in the cache are lost.
Example: Specifying an Identity Column as a Unique Primary Index Using CYCLE
This example also creates an identity column that is used as the primary index for the table. Values for idnum are generated starting with 1,000 and decremented by 10 until the specified MINVALUE of 1 is reached. Because CYCLE is specified, no error is returned and numbering proceeds as follows: 1,000, 990, 980 … 10, 100,000, 99,990, 99,980 …
However, if an idnum value is still in existence when an attempt is made to recycle it, the request aborts because idnum is the UPI for the table, so all its values must be unique.
CREATE TABLE t ( idnum INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY -10 MINVALUE 1 MAXVALUE 100000 CYCLE), phone INTEGER) UNIQUE PRIMARY INDEX(idnum);
Example: Specifying a Primary Index and a Primary Key
The request in this example specifies both a primary index and primary key.
The Primary Key (column_1 ) is mapped to a unique secondary index. The UNIQUE constraint on column_3 also is mapped to a USI. These two unique secondary indexes cannot be null. The Unique Primary Index (column_2 ) can be null; however, no more than one row in the table can have a null UPI value.
CREATE TABLE good_5 ( column_1 INTEGER NOT NULL PRIMARY KEY, column_2 INTEGER, column_3 INTEGER NOT NULL UNIQUE, column_4 INTEGER) UNIQUE PRIMARY INDEX (column_2);
Example: Primary Index with Column Partitioning
Following is an example of a CREATE TABLE statement for a CP table with a primary index:
CREATE TABLE Sales4 ( storeid INTEGER NOT NULL, productid INTEGER NOT NULL, salesdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, totalrevenue DECIMAL(13,2), note VARCHAR(256) ) PRIMARY INDEX (storeid, productid) PARTITION BY COLUMN;
By default for this table, each column is in a separate column partition and each of the column partitions have COLUMN format with autocompression. For the following query, this allows a single-AMP step with hashed access on this AMP and column partition elimination. In step 1, for a single hash value, three user column partitions and the delete column partition are accessed on this single AMP. While this can provide efficient access, row header compression and autocompression might not be as effective as a CP table with a primary AMP index, especially if the primary index is nearly unique.
EXPLAIN SELECT SUM(totalrevenue) FROM Sales4 s WHERE s.storeid = 37 AND s.productid = 1466; Explanation --------------------------------------------------------------------------- 1) First, we do a single-AMP SUM step to aggregate from 4 column partitions of PLS.s by way of the primary index "PLS.s.storeid = 37, PLS.s.productid = 1466" with no residual conditions. Aggregate Intermediate Results are computed locally, then placed in Spool 3. The size of Spool 3 is estimated with high confidence to be 1 row (23 bytes). The estimated time for this step is 0.02 seconds. 2) Next, we do a single-AMP RETRIEVE step from Spool 3 (Last Use) by way of the hash value of "PLS.s.storeid = 37, PLS.s.productid = 1466" into Spool 1 (one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with high confidence to be 1 row (36 bytes). The estimated time for this step is 0.02 seconds. 3) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.04 seconds.
Example: Unique Primary Index with Column Partitioning
This example defines a column-partitioned table with a unique primary index. Each column is in its own partition with system-determined ROW format. A 2-byte partitioning is defined. By default, a column-partitioned table is a multiset table. MULTISET can be explicitly specified but SET must not be specified. The number of bytes per row is increased by 86 due to the row headers per column partition. This example assumes autocompression is the default for column partitions.
CREATE TABLE Orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHAR(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_shippriority INTEGER, o_comment VARCHAR(79) ) UNIQUE PRIMARY INDEX (o_orderkey) PARTITION BY COLUMN;