|
|
|
Forum Member
 
Group: Forum Members
Last Login: 8/7/2009 9:02:17 AM
Posts: 36,
Visits: 53
|
|
Hey!
Just trying to understand the workings of the secondary index.
I understand the concepts of 2-AMP and All-AMPs Retrieve, I am just trying to understand how the actual secondary index subtable is stored.
Is the USI subtable stored on one AMP and one AMP only, or is it duplicated across all AMPs? I understand that the NUSI subtable is stored on all AMPs and that each AMP stores its own values, so this is an All-AMPs Retrieve as it scans all of the AMPs' subtables and then those AMPs with matching values return their rows.
But how does the USI subtable work?
Thanks!
Andrew
Andrew C. Livingston
Teradata Certified Professional and Trainer
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 8/31/2009 4:49:43 PM
Posts: 50,
Visits: 234
|
|
USI table is stored only in one AMP.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hey Andrew,
The USI subtable can be stored in any number of AMP.
Each AMP has its own part of USI subtable…
The key here is row id will be unique in all subtable and we no need to refer all USI subtable for single row retrieval.
When we performing hashing algorithm, the hash map points to only one AMP containing the subtable row corresponding to the row hash value. Then the base row is retrieved from an AMP which the subtable indicates.
The point to be noted here is the subtable AMP id differs for different row hash values.
Regards,
Balamurugan
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 8/31/2009 4:49:43 PM
Posts: 50,
Visits: 234
|
|
Hi andrew,
I am sorry for giving a wrong reply. Balamurugan is correct. Each AMP will contain it's part of USI subtable. The main difference in distribution of the main table and subtable is: main table is distributed based on hash value of PI but USI subtable is distributed based on USI value.
Balamurugan: Thanks for the correction!!
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/28/2007 5:04:36 PM
Posts: 5,
Visits: 4
|
|
|
The row hash for a secondary index is usually different from the row hash for the primary index on the same table, unique secondary indexes are generally stored on a different AMP than the row they point to. Nonunique secondary indexes are not hashed and are always stored on the same AMP as the rows they point to.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Just to add to the above comments… USI can be a 1 AMP or 2 AMP operations. i.e. typically it is a 2 AMP operation, but there is a possibility of subtable row and base table row can store in the same AMP, since both are hashed separately. In this case it is a one AMP operation.
The NUSI will always be hashed whenever it is used. Without hashing it is not possible to point to the respective Row ID in NUSI subtable in any AMP.
Regards,
Balamurugan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
Any SI-subtable is just a table.
CREATE UNIQUE INDEX USI(id int) ON tab;
creates a table exactly like
CREATE TABLE USI(
id int,
overhead byte(7), -- might be > 7
baseROWID byte(8) -- or byte(10) if base table is partitioned
)
UNIQUE PRIMARY INDEX(id);
CREATE INDEX NUSI(id int) ON tab;
creates a table exactly like
CREATE TABLE NUSI(
id int,
overhead byte(7), -- might be > 7
baseROWIDs array of byte(8) -- or byte(10) if base table is partitioned
)
NON-HASHED AMP-LOCAL PRIMARY INDEX(id);
Of course NON-HASHED AMP-LOCAL is not a valid option for a PI, but the non-hashed system tables like dbc.databasespace are exactly the same.
Any access to a hash-ordered SI results in hashing the SI-value, the difference is just: sending the message to a single AMP (USI) or all AMPs (NUSI).
When accessing a value ordered NUSI the SI-value is not hashed.
A USI access is a two AMP access, even if the base row resides on the same AMP, because the AMP doesn't check for this, but simply passes a message to the BYNET, which is redirected back to that AMP.
Nevertheless single AMP access is possible for both USI/NUSI when it's based on the tables PI, then the optimizer knows about that.
Dieter
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi dieter,
Thanks for your inputs!!
It’s now very clear about USI and NUSI access…
Regards,
Balamurugan
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 7/27/2009 12:12:46 PM
Posts: 67,
Visits: 133
|
|
Hi Dieter,
Kindly let me know what you mean by this statement
"Nevertheless single AMP access is possible for both USI/NUSI when it's based on the tables PI, then the optimizer knows about that"
Regards,
Annal T
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/21/2009 2:37:03 AM
Posts: 1,
Visits: 3
|
|
Hi,
I guess he means that NUSI/USI operation can be 1 AMP if it is also the PI of the table.
In that case the optimizer would now its a PI and will thus be a 1 amp operation.
I have a question though on the subtables created for SI.
Why is the storage of subtable 'AMP LOCAL' for NUSI?
Thanks
Shalini
|
|
|
|