Saturday 16 March 2013

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
  1. 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;

No comments:

Post a Comment