Value-Ordered NUSIs and Range Conditions | Teradata Vantage - Value-Ordered NUSIs and Range Conditions - Advanced SQL Engine - Teradata Database

Value-ordered NUSIs are very efficient for range conditions. Because the NUSI rows are sorted by data value, it is possible to search only a portion of the index subtable for a given range of key values.

Limitations

• The sort key is limited to a single numeric or DATE column.
• The sort key column cannot exceed four bytes in length.
• If defined over multiple columns and with an ORDER BY clause, they count as two consecutive indexes against the total of 32 non-primary indexes you can define on a base or join index table. This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.

  One index represents the column list and the other index represents the ordering column.

Importance of Consecutive Indexes for Value-Ordered NUSIs

The system automatically assigns incrementally increasing numbers to indexes when they are created on a table. This is not important externally except for the case of composite value-ordered NUSIs, because these indexes not only consume two of the allotted 32 index numbers from the pool, but those two index numbers are consecutive. One index of the consecutively numbered pair represents the column list, while the other index in the pair represents the ordering column.

To understand why this is important, consider the following scenario.

1. You create 32 indexes on a table, none of which is value-ordered.

   This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.

2. You drop every other index on the table, meaning that you drop either all the odd-numbered indexes or all the even-numbered indexes.

   For example, if you had dropped all the even-numbered indexes, there would now be 16 odd-numbered index numbers available to be assigned to indexes created in the future.

3. You attempt to create a value-ordered multicolumn NUSI.

   The request aborts and the system returns an error message to you.

   The reason the request aborts is that two consecutive index numbers were not available for assignment to the composite value-ordered NUSI.

   You are still able to create 16 additional non-value-ordered NUSIs, single-column value-ordered NUSIs, USIs, hash indexes, or join indexes, but you cannot create any composite value-ordered NUSIs.

To work around this problem, perform the following procedure.

1. Drop any index on the table.

   This action frees 2 consecutive index numbers.

2. Create the value-ordered multicolumn NUSI that failed previously.
3. Recreate the index you dropped to free the consecutive index numbers.

Here is a simple example of why value-ordered composite NUSIs consume two consecutive index numbers from the total available number of 32.

First define a table on which a NUSI is to be defined.

CREATE MULTISET TABLE transsupplier (
  suppkey INTEGER NOT NULL,
  name    CHAR(25) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
  seccode INTEGER NOT NULL,
  groupID INTEGER NOT NULL)
UNIQUE PRIMARY INDEX ( suppkey );

Then you create an ordered composite NUSI on seccode and groupID, ordering on groupID.

CREATE INDEX (seccode, groupID)
ORDER BY VALUES (groupID) ON transsupplier;

You find two rows in DBC.Indexeswith an IndexType code of V (meaning a value-ordered secondary index) and an IndexNumber of 4 for this composite NUSI. The third row, with an IndexType of I and an IndexNumber of 8, represents the fact that the groupID column is the ordering column of the two columns making up the composite NUSI, so a single ordered composite NUSI uses up two of the available 32 secondary, hash, or join indexes that can be defined for TransSupplier.

SELECT s.CreateTimestamp, s.IndexType, s.IndexNumber, t.TVMName,
       s.FieldName
FROM DBC.Indexes AS s, DBC.TVM AS t
WHERE s.tableID = t.TVMID
AND   s.databaseID = t.databaseID
AND   s.fieldID = s.fieldID
AND   s.tableID = s.tableID
AND   s.databaseID = s.databaseID
AND   s.indextype <> 'P'
AND   t.TVMName = 'Transsupplier'
ORDER BY s.createtimestamp DESC;

2007-04-27 14:51:55

I

8

TransSupplier

GROUPID

2007-04-27 14:51:55

V

4

TransSupplier

GROUPID

2007-04-27 14:51:55

V

4

TransSupplier

SECCODE

Suppose you had instead created the following ordered single-column NUSI on TransSupplier, this time on groupID only, ordering on groupID.

CREATE INDEX (groupID)
ORDER BY  values (groupID) ON transsupplier;

You then run the same query against DBC.Indexes.

SELECT i.CreateTimestamp, i.IndexType, i.IndexNumber, t.TVMName,
       f.FieldName
FROM DBC.Indexes AS i, DBC.TVM AS t, DBC.TVFields AS f
WHERE i.tableID = t.TVMID
AND   i.databaseID = t.databaseID
AND   i.fieldID = f.fieldID
AND   i.tableID = f.tableID
AND   i.databaseID = f.databaseID
AND   i.indextype <> 'P'
AND   t.TVMName = 'Transsupplier'
ORDER BY i.createtimestamp DESC;

The query produces a report something like this, where you find only one row in DBC.Indexes with an IndexType code of V for this single-column NUSI, meaning that only one of the possible 32 secondary, hash, and join indexes has been consumed by the creation of this NUSI.

2007-04-27 14:56:24

V

4

TransSupplier

GROUPID

Typical Uses of Value-Ordered and Hash-Ordered NUSIs

The typical use of a hash-ordered NUSI is with an equality condition on the secondary index column set. The specified secondary key value is hashed, and then each NUSI subtable is probed for rows having the same row hash. For every matching NUSI entry, the corresponding rowIDs are used to access the base rows on the same AMP. Because the NUSI rows are stored in row hash order, searching the NUSI subtable for a particular row hash value is very efficient.

Value-ordered NUSIs, on the other hand, are useful for processing range conditions and conditions with either an equality or inequality on the ordering column of the secondary index.

The Optimizer can select hash-ordered NUSIs to access rows for range conditions using a full-table scan of the NUSI subtable to find the matching secondary key values.

With a value-ordered NUSI, it is possible to search only a portion of the index subtable for a given range of key values.