16.10 - Using Hash Index and Join Index Row Compression - 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)

Row compression automatically divides rows into repeating and nonrepeating portions. The system appends multiple sets of nonrepeating column values to a single set of repeating column values. This allows the system to store the repeating value set only once, while any nonrepeating column values are stored as logical extensions of the base repeating set. The nonrepeating column set has a pointer to each of the repeating column sets so it can reconstruct the rows when required.

Row compression significantly reduces index storage space for index columns that have a large number of repeating values.

For hash indexes, the system compresses rows by default.

For join indexes you can:

  1. Specify the columns to be compressed using the (column_1_name) syntax elements in the SELECT clause of a CREATE JOIN INDEX statement.
  2. Specify the columns to remain uncompressed using the (column_2_name) syntax elements in the SELECT clause for the same CREATE JOIN INDEX statement.

Example 1: Join Index Row Compression

Assume that a join index specifies 5 columns:

  • Two columns with repeating values:
    • Column A indicates whether the customer owns a truck or car
    • Column B indicates whether the vehicle is domestic or foreign made
  • Three columns of unique, or mostly unique, information:
    • Column C is the customer name
    • Column D is the customer address
    • Column E is the customer phone number

The uncompressed join index row data is as follows:

(car,foreign,joe,addr1,ph1)
(car,foreign,jane,addr2,ph2)
(car,foreign,max,addr3,ph3)
(car,domestic,bill,addr6,ph6)
(car,domestic,linda,addr7,ph7)
(truck,domestic,brad,addr4,ph4)
(truck,domestic,sam,addr5,ph5)
(truck,foreign,dave,addr8,ph8)
(truck,foreign,rick,addr9,ph9)

You can specify row compression in a CREATE JOIN INDEX statement, for example:

CREATE JOIN INDEX  ji_name  AS
     SELECT (col_a,col_b),(col c,col_d,col_e)
     FROM  table_name 
     . . . ;

where the first set of parentheses defines the columns (col_a and col_b) for which duplicate row values are compressed, and the second set of parentheses defines the remaining columns (col_c, col_d, and col_e), for which row values are not compressed.

As a result, the system stores each possible combination of the repeating values for col_a and col_b once, grouped with the related row values for the uncompressed columns, for example:

((car,foreign),(joe,addr1,ph1),(jane,addr2,ph2),(max,addr3,ph3))
((car,domestic),(bill,addr6,ph6),(linda,addr7,ph7))
((truck,domestic),(brad,addr4,ph4),(sam,addr5,ph5))
((truck,foreign),(dave,addr8,ph8),(rick,addr9,ph9))

Benefits

Row compression significantly reduces index storage space for index columns that have a large number of repeating values.

Usage Notes

Row compression is valuable where column values repeat from row to row. Columns in a join index that have a high percentage of distinct (non-repeating) values are not a good candidates for row compression. Row compression cannot be used with data using row-level security constraints. A join index with row compression cannot be column-partitioned. A row-compressed JI cannot be used for a query with outer joins, queries with complicated correlated subqueries, and single-table OLAP queries.

In addition to any row compression you define for a join index, the index also inherits MVC compression defined for its base tables, with some exceptions.

Related Topics

For information on... See...
strategies and restrictions when specifying row compression in a join index, including how join indexes inherit MVC from base tables “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics
syntax and examples for using the CREATE JOIN INDEX statement “CREATE JOIN INDEX” in SQL Data Definition Language Syntax and Examples
join index row compression as it relates to database design Join Index Storage