Toad World® Forums

Index Options Tab - Teradata


#1

What is the purpose of the Index Options tab on the Index screen for Teradata? How is it used?


#2

Hi Adam.
in Teradata, there are 4 index types available- see the first screenshot.

For Join and Hash indexes, options are loaded when RE and options can be stated here when building a model and generating script.

**EXAMPLES:

  1. Hash Index**
    CREATE statement

CREATE HASH INDEX OrdHIdx_1 (o_orderdate) ON orders
BY (o_orderdate)
ORDER BY (o_orderdate);

Index Options tab in TDM shows options

BY (o_orderdate)
ORDER BY (o_orderdate);

2. Join Index
CREATE statement

CREATE JOIN INDEX OrdJIdx1
AS
SELECT (o_orderdate), (o_orderkey, orders.ROWID)
FROM orders
ORDER BY o_orderdate
PRIMARY INDEX (o_orderdate);

Index Options tab in TDM shows options

AS
SELECT (o_orderdate), (o_orderkey, orders.ROWID)
FROM orders
ORDER BY o_orderdate
PRIMARY INDEX (o_orderdate);

Regards,
Lukas

teradata_index_options.png


#3

Hi Adam.
in Teradata, there are 4 index types available- see the first screenshot.

For Join and Hash indexes, options are loaded when RE and options can be stated here when building a model and generating script.

**EXAMPLES:

  1. Hash Index**
    CREATE statement

CREATE HASH INDEX OrdHIdx_1 (o_orderdate) ON orders
BY (o_orderdate)
ORDER BY (o_orderdate);

Index Options tab in TDM shows options

BY (o_orderdate)
ORDER BY (o_orderdate);

2. Join Index
CREATE statement

CREATE JOIN INDEX OrdJIdx1
AS
SELECT (o_orderdate), (o_orderkey, orders.ROWID)
FROM orders
ORDER BY o_orderdate
PRIMARY INDEX (o_orderdate);

Index Options tab in TDM shows options

AS
SELECT (o_orderdate), (o_orderkey, orders.ROWID)
FROM orders
ORDER BY o_orderdate
PRIMARY INDEX (o_orderdate);

Regards,
Lukas

teradata_index_type.png


#4

Maybe this could be used to accomodate Partitioned Primary Indexes and Multi-Level Partitioned Primary Indexes?


#5

Hi,

For now this doesn’t work, but I created an enhancement request ST99149 to make it possible to use Index Options tab for the body of Partitioned Primary key and Multi-level Partitioned Primary Indexes.

Thank you for your suggestions!

Regards,
Lukas


#6

Hello,

We recently purchased Toad Data Modeler with the understanding it supported Teradata. How does is support partitioning without using the Primary Partition Options Tab ? Currently, the Primary Index Options are being ignored in the SQL generation. Would this not be a bug ?

Thanks,
Kurt


#7

Hi Kurt,

the enhancement request was registered under #107088 and was closed as finished. The feature will appear in next commercial version.

Thank you,

Vaclav