Interview Questions   Tutorials   Discussions   Programs   

Teradata - What is Value-Ordered NUSIs ?




614
views
asked vishnoiprem August 17, 2014 05:51 AM  

What is Value-Ordered NUSIs ?


           

1 Answers



 
answered By vishnoiprem   0  
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

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. 

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. 


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

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.

flag   
   add comment

Your answer

Join with account you already have

FF

Preview

 Write A Tutorials
Online-Classroom Classes
www.writeabc.com


  1 person following this question

  1 person following this tag

  Question tags

teradata × 1

Asked 3 years and 3 months ago ago
Number of Views -614
Number of Answers -1
Last updated
3 years and 3 months ago ago

  Similar questions


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert