For Teradata's file system there's no difference between a base table and a index subtable, both consist of rows sorted by hash. The only difference: A NUSI row is hashed, but not distributed by hash value to the AMPs.
If a NUSI was distributed like a base table (or a USI) this could result in a heavily skewed table with inefficient access.
Dieter
Hi dnoeth,
When we define particular column as NUSI, Sub table is created with its value & its base row id. Also, both subtable & its corresponding base row would be placed in a same AMP. How could it be done?? Is there any internal mechanism?
Thanks!
The internal mechanism is "don't distribute the row based on hash, just keep it local".
You might check the "Database Design" manual, which covers everything in detail.
Dieter
A single table join index can have a PI different from the underlying table. You can refer back to the rows by using ROWIDS form or you can cover the columns that will be used in the common queries.
Hi,
So far my understanding about NUSI
- Both Base table & Subtable belong to a single row resides on same AMP.
- Subtable can only check base-row-id that local to the same AMP.
- Its ALL AMP operation.
- NUSI column is not HASHED.
Doubt:
- How does Teradata create subtable in the same AMP as of its base row?
- Why is that not NUSI column is not hashed. Access would have been much easier if the NUSI column is hashed since it would be a SINGLE AMP operation?
Thanks!
Mahs