15.10 - Possible Recommendations from Index Wizard or INITIATE PARTITION ANALYSIS - Teradata Database

This topic provides examples of partitioning expressions that the Index Wizard and INITIATE PARTITION ANALYSIS might recommend, as well as example that the Index Wizard and INITIATE PARTITION ANALYSIS never recommend.

The following SQL DDL text defines the tables used in the examples that follow.

  CREATE TABLE sales_history (

    product_code CHARACTER(8),

    store_number INTEGER,

    transaction_date DATE,

    quantity_sold INTEGER,

    other_columns CHARACTER(50))

  PRIMARY INDEX(product_code,store_number,transaction_date);

  CREATE TABLE products (

    product_code CHARACTER(8),

    description VARCHAR(50),

    prod_division_number INTEGER,

    product_category CHARACTER(10),

    price DECIMAL(10,2),

    quantity_avail INTEGER)

  PRIMARY INDEX(product_code);

  CREATE TABLE call_detail (

    phone_number DECIMAL(10),

    call_date DATE,

    call_start TIMESTAMP,

    call_duration INTEGER,

    caller_id INTEGER,

    other_columns CHARACTER(30))

PRIMARY INDEX(phone_number,call_date)

PARTITION BY RANGE_N(CAST phone_number/100000.00000 AS INTEGER)

                     BETWEEN 0

                     AND 99999

                     EACH 2);

  CREATE TABLE supplier (

    supplier_code CHARACTER(8),

    supp_region_code INTEGER,

    phone_number DECIMAL(10),

    supplier_name VARCHAR(50),

    other_columns CHARACTER(30))

  UNIQUE PRIMARY INDEX(supplier_code);

The following expressions are examples of partitioning expressions that Partition Analysis might recommend for the tables defined in “About Possible Recommended Partitioning Expressions.” Note that the recommended partitioning expressions are all based on the RANGE_N function. Teradata Index Wizard only recommends partitioning expressions that are based on RANGE_N.

PARTITION BY RANGE_N(transaction_date

                     BETWEEN DATE '2002-01-01'

                     AND DATE '2006-12-31'

                     EACH INTERVAL ‘7’ DAY);

PARTITION BY RANGE_N(transaction_date

                     BETWEEN DATE '2002-01-01'

                     AND DATE '2006-12-31'

                     EACH INTERVAL ‘1’ MONTH);

PARTITION BY RANGE_N(store_number

                     BETWEEN 100

                     AND 999

                     EACH 1);

PARTITION BY RANGE_N(store_number

                     BETWEEN 100

                     AND 999

                     EACH 10);

Note that in the following example, the column prod_division_number is not a component of the NUPI for the products table:

PARTITION BY RANGE_N(prod_division_number

                     BETWEEN 1

                     AND 99

                     EACH 1);

Note that in the following example, the system recommends a change to the existing partitioning expression for the call_detail table:

PRIMARY INDEX(phone_number, call_date)

PARTITION BY RANGE_N(call_date

                     BETWEEN DATE '2002-01-01'

                     AND DATE '2006-12-31'

                     EACH INTERVAL ‘7’ DAY);