Tools & Utilities

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-25-2007
02:56 PM

11-25-2007
02:56 PM

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

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

9 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-26-2007
11:57 AM

11-26-2007
11:57 AM

USI table is stored only in one AMP.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-27-2007
01:48 AM

11-27-2007
01:48 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-27-2007
10:20 AM

11-27-2007
10:20 AM

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!!

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!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-28-2007
05:07 PM

11-28-2007
05:07 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-29-2007
12:39 AM

11-29-2007
12:39 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-29-2007
02:07 AM

11-29-2007
02:07 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-29-2007
02:19 AM

11-29-2007
02:19 AM

Hi dieter,

Thanks for your inputs!!

It’s now very clear about USI and NUSI access…

Regards,

Balamurugan

Thanks for your inputs!!

It’s now very clear about USI and NUSI access…

Regards,

Balamurugan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-29-2007
12:04 PM

11-29-2007
12:04 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-04-2009
05:34 AM

09-04-2009
05:34 AM

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?

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?