Indexes - Part 1 - Creating and Dropping NUSI

NUSI (Non Unique Secondary Indexes)

• Used to improve performance of queries that use the column or columns in WHERE clause.
   

• NUSI can be created in following manners

1. When creating the table using CREATE TABLE syntax

2. Using CREATE INDEX after the table is created.

• NUSI can be dropped at any moment of time.

• Syntax for creating index while creating table

CREATE SET TABLE EDW_RESTORE_TABLES.EMPLOYEE ,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);

• Syntax for creating index using CREATE INDEX

CREATE INDEX (DEPARTMENTNO) ON EMPLOYEE;

• Syntax for dropping index using DROP INDEX

DROP INDEX (DEPARTMENTNO) ON EMPLOYEE;