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