NUSI Subtable

Database
Enthusiast

NUSI Subtable

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

4 REPLIES
Junior Contributor

Re: NUSI Subtable

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

Enthusiast

Re: NUSI Subtable

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!

Junior Contributor

Re: NUSI Subtable

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

Teradata Employee

Re: NUSI Subtable

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.