Indexes - Part 2 - Creating and Dropping Value Ordered NUSI

Value Ordered NUSI:

• When we create a NUSI on the table a subtable is built on all AMP's. Each Subtable contains a row for the NUSI and corresponding row ids to the base rows located on the same AMP.

Rows in the subtable are sequenced by the row hash of the NUSI value. This way of storing rows is convenient in storing rows with particular NUSI, but not good for range queries.

This is where Value Ordered NUSI becomes useful

• VALUE ordered NUSI allows rows in the subtable to be stored in order of data value , rather than hash of the value. This is useful when performing range queries.

• Creating value ordered NUSI when creating table.

Example 1:

CREATE TABLE EMPLOYEE1

(

Employeeid integer,

DepartmentNo integer,

Salary decimal(8,2),

Hiredate date

) INDEX (Departmentno) ORDER BY VALUES;

Doing a show table will show below:

SHOW TABLE EMPLOYEE1

CREATE SET TABLE EDW_RESTORE_TABLES.EMPLOYEE1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Employeeid INTEGER,

      DepartmentNo INTEGER,

      Salary DECIMAL(8,2),

      Hiredate DATE FORMAT 'YYYY/MM/DD')

PRIMARY INDEX ( Employeeid )

INDEX ( DepartmentNo ) ORDER BY VALUES ( DepartmentNo ); --> note that DEPARTMENTNO is used to order. Here since we had only one column in the index, it is used for ordering.

However when we have index made of multiple columns we can choose which column to use fo ordering.

Example 2:

CREATE TABLE EMPLOYEE1

(

Employeeid integer,

DepartmentNo integer,

Salary decimal(8,2),

Hiredate date

) INDEX (Departmentno,Employeeid) ORDER BY VALUES;

/* Multiple columns as part of INDEX*/

SHOW TABLE EMPLOYEE1

CREATE SET TABLE EDW_RESTORE_TABLES.EMPLOYEE1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Employeeid INTEGER,

      DepartmentNo INTEGER,

      Salary DECIMAL(8,2),

      Hiredate DATE FORMAT 'YYYY/MM/DD')

PRIMARY INDEX ( Employeeid )

INDEX ( Employeeid ,DepartmentNo ) ORDER BY VALUES ( DepartmentNo ); --> by default system choose the 1st column DEpartmentNo for ordering.

In case of multiple columns being part of index we can specify the column which we would want to be used for ordering as shown in below example:

Example 3:

CREATE TABLE EMPLOYEE1

(

Employeeid integer,

DepartmentNo integer,

Salary decimal(8,2),

Hiredate date

) INDEX (Departmentno,Employeeid) ORDER BY VALUES(employeeid);

• Creating Value ordered NUSI using CREATE INDEX syntax

CREATE INDEX(DepartmentNO) ORDER BY VALUES (DepartmentNO) on EMPLOYEE ;

• Rules for using value ordered NUSI

1. The ordering can be done only on 1 single column.

In below we try to create a value ordered NUSI with two columns

CREATE TABLE EMPLOYEE1

(

Employeeid integer,

DepartmentNo integer,

Salary decimal(8,2),

Hiredate date

) INDEX (Departmentno,Employeeid) ORDER BY VALUES(Departmentno,Employeeid) ;

Following is the error message we get:

CREATE TABLE Failed. 5466:  Error in Secondary Index DDL, Order by clause can have only one column. 

2. Column used for ordering must a part or all of the index definitions

In the following query we are ordering by a column that is not a part of index definition

CREATE TABLE EMPLOYEE1

(

Employeeid integer,

DepartmentNo integer,

Salary decimal(8,2),

Hiredate date

) INDEX (Departmentno,Employeeid) ORDER BY VALUES(salary) ;

Following is the error message we get:

CREATE TABLE Failed. 5466:  Error in Secondary Index DDL, Order by field does not belong to the index. 

3. The column should be numeric- non numeric columns cannot be used.

4. Column should not be greater than 4 bytes. Thus only INT, SMALLINT, BYTEINT, DATE, DEC are valid. A Decimal is also allowed provided its storage length does not exceed 4 bytes and it does not have any precision digits.