All Forums Database
Mahs 32 posts Joined 04/11
19 May 2012
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

dnoeth 4628 posts Joined 11/04
20 May 2012

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

Dieter

Mahs 32 posts Joined 04/11
21 May 2012

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!

dnoeth 4628 posts Joined 11/04
21 May 2012

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

Dieter

ToddAWalter 316 posts Joined 10/11
25 May 2012

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.

You must sign in to leave a comment.