Indexes - Part 3 - Index Covering

What is Index Covering:

If the query references only those columns that are contained within a given index, then the query is said to be covering.

Such cases are more efficient because the optimizer has to access only the index subtable and does not need to access the base table rows at all.

Covering will be considered for any query that references only columns defined in a given NUSI. These columns can be specified anywhere in the query including the:

• SELECT list

• WHERE clause

• aggregate functions
• GROUP BY
• expressions

However having all the indexed columns does not guarantee that the optimizer will use index to cover the query. Whether or not the index is used we can check after looking at the explain plan

Example 1:

create index (departmentno) order by values on employee;

EXPLAIN select departmentno from employee where departmentno != 400;

Explain select departmentno from employee where departmentno != 400

  1) First, we lock a distinct MY_TEST_DB."pseudo table" for

     read on a RowHash to prevent global deadlock for

MY_TEST_DB.employee.

  2) Next, we lock MY_TEST_DB.employee for read.

  3) We do an all-AMPs RETRIEVE step from MY_TEST_DB.employee

     by way of a traversal of index # 4 without accessing the base 

     table with a residual condition of (

     "MY_TEST_DB.employee.DepartmentNo <> 400") into Spool 1

     (all_amps), which is built locally on the AMPs.  The size of Spool

     1 is estimated with no confidence to be 1,296 rows (32,400 bytes).

     The estimated time for this step is 0.01 seconds.

  4) 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.01 seconds.

Note the line where explain plan explicitly says that it would not access the base table.

This index is covering the query.

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.