15.10 - Applying the Index and Partitioning Expression Recommendations - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

At this stage of the database query analysis, you apply the indexes and partitioning expressions recommended by the Index and Partitioning Expression Analysis stage to the production database.

See Teradata Index Wizard User Guide for details about this process.

The process of applying the recommended indexes, or partitioning expressions, or both, to the production system is indicated by the following graphic. For any callout of Index in the graphic, you should read Index or Partitioning Expression, or both.

 

You can choose to implement all, some, or none, of the index or partitioning expression recommendations from a given Analysis.

In the case of indexes, the Index Wizard client utility has a menu option that automatically issues an SQL request containing the corresponding CREATE INDEX or CREATE JOIN INDEX text for you.

A recommended table partitioning requires a more complex set of SQL requests to recreate a populated table. The Index Wizard client utility provides a set of SQL requests in the form of a script to demonstrate one possible method of converting an existing table to a row‑partitioned table.

You are expected to examine and modify the script for the specific needs of your particular system. For example, you must verify that there is enough available disk space to temporarily hold two copies of the table, and you must also determine what privileges must be granted again and which existing indexes must be recreated.

You can indirectly execute the script by copying the displayed contents to the Execute SQL window that is accessible from the Tools menu of the Index Wizard.

The script creates a new permanent table, partitions it as desired, executes an appropriate INSERT … SELECT request to insert the rows from the nonpartitioned source table into it, drops the source table, and then renames the new table with the name of the old source table.

To reduce the possibility of naming collisions, the new table (before renaming) exists temporarily, before it is renamed, with a name of PPI_base_table_name where base_table_name is truncated if necessary.

See “Using the Index Wizard: Extended Example” on page 852 for an example of using this method.