16.10 - Value-Ordered NUSIs and Range Conditions - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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.

Note that for most applications, a partitioned primary index on a join index is a better choice to handle range conditions than a value-ordered NUSI (see Designing for Range Queries: Guidelines for Choosing Between a PPI and a Value-Ordered NUSI.

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;
CreateTimeStamp IndexType IndexNumber TVMName FieldName
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

Note that Index Number 1 is reserved for the primary index of a table (which is suppkey in the example, but is not included in the report to simplify its meaning), and subsequent indexes are numbered beginning with 4 in increments of 4, so indexes 4 and 8 are consecutively numbered.

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.

CreateTimeStamp IndexType IndexNumber TVMName FieldName
2007-04-27 14:56:24
V
4
TransSupplier
GROUPID

Index number 1, for primary index column suppkey, is not displayed in the report in order to simplify the point of the example.

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 secondary index column set.

Although hash-ordered NUSIs can be selected by the Optimizer to access rows for range conditions, a far more common response is to specify a full-table scan of the NUSI subtable to find the matching secondary key values. Therefore, depending on the size of the NUSI subtable and the number of qualified rows, this might not be very efficient.

By sorting the NUSI rows by data value, it is possible to search only a portion of the index subtable for a given range of key values. The Optimizer must still estimate the selectivity of a NUSI to be high for it to cost less than a full-table scan. The major advantage of a value-ordered NUSI is in the performance of range queries.

Example

The following example illustrates a value-ordered NUSI (defined by an ORDER BY clause that specifies the VALUES keyword option on o_orderdate) and a query that would probably be solved more efficiently if the specified value-ordered NUSI were selected by the Optimizer to access the requested rows.

     CREATE INDEX Idx_Date (o_orderdate)
     ORDER BY VALUES (o_orderdate)
     ON Orders;
     SELECT *
     FROM Orders
     WHERE o_orderdate
     BETWEEN DATE ‘2005-10-01’
     AND     DATE ‘2005-10-07’;