Value ordered NUSI

Database
Enthusiast

Value ordered NUSI

I have some doubts on value ordered NUSI.

1.       Can we create a NUSI on a column that already has a Value-Ordered NUSI defined on the same column? In that case will it have different sub-tables?

2.       For below example EMP No is UPI and Joining Date is value ordered NUSI of EMP table.

EMP_No      EMP_Name            Joining_Date

   1                  ABC                      01-01-2012

   2                  XYZ                      01-01-2012

   3                  MNV                     01-02-2012

   4                  RST                      01-05-2011

Suppose it has 2 AMPs and in that case each AMP will get 2 rows. In that case how will we get the benefit of VO NUSI for below query?

SELECT * FROM EMP

WHERE JOINING DATE BETWEEN 01-01-2012 AND 01-03-2012;

3 REPLIES
Enthusiast

Re: Value ordered NUSI

Hi,

NUSI is always an all amp operation but not a full table scan.

So it will first ask the subtables on all the amps and querying this subtable based on a non eqality condition

as it is in the case above will be fatser when the rows in the sub table are value orderd (date column above) rather than

hash order as in case of normal NUSI

So the advantage lies in how quick it can get the info from the sub tables.

if your where clause is based on a equilty condition then hash orderd NUSI will be fatser however if its based on a range

then value ordered NUSI will be faster as it need not scan the entire subtable.

Regards

R.Rajeev

Enthusiast

Re: Value ordered NUSI

Hi Rajeev,

Thanks for reply. NUSI sub-tables are AMP local and not hash distributed. Now if the values of the NUSI column in a particular range is located indifferent AMPs, in that case how value ordered NUSI will benefit?

Enthusiast

Re: Value ordered NUSI

Hi Moutashi,

As i said first all NUSI operations are all amp be it value ordered or non the advantage lies in reading the data out of subtable.

Terdata works in parallel so it doesnt matter if data are on diff amps the request is processed parallely, the difference lies in how quickly it can read the subtables once it has identified the appropriate amps thats where the value ordered NUSI is faster for the range query on the secondary index column.

Regards

R.Rajeev