karunakar

• Oct 14th, 2006

 

Hi All,

How does indexing improve query performance in teradata and oracle?

reagards,

karunakar

             

Anitha

• Mar 7th, 2007

 

Indexing is a way to physically reorganise the records to enable some frequently used queries to run faster.


The index can be used as a pointer to the large table. It helps to locate the required row quickly and then return ot back to the user.

or

The frequesntly used queries need not hit a large table for data. they can get what they want from the index itself. - cover queries.

Index comes with the overhead of maintanance. Teradata maintains its index by itself. Each time an insert/update/delete is done on the table the indexes will also need to be updated and maintained.

Indexes cannot be accessed directly by users. Only the optimizer has access to the index.

Sangra

 Profile Answers by Sangra Questions by Sangra

• Feb 7th, 2010

 

Indexes in Teradata are bit different than Oracle.

Primary Index(PI) in Teradata is solely related to the data distribution i.e. decide how evenly the data can be distributed across all AMPs.

Primary Index with Unique attribute or Primary Key (PK) becomes Unique Primary Index(UPI)... i.e. (UPI=PI+PK/Unique Attr). Uniqueness of the Primary index is ensured by additional index on the same column which is a Unique Secondary Index(USI). Therefore UPI automatically creates USI on the same column (UPI>creates>USI).

The Secondary Indexes are the traditional indexes which are basically pointers to the original row.

d

• Jun 25th, 2014

 

High Confidence: The stats are collected

Low Confidence : Stats are not collected but the used the index columns in where condition and join conditions

No Confidence : No stats and not used the index columns in where and join conditions

Kashyap

• Aug 24th, 2015

 

The following table shows how the Teradata Database optimizer can use indexes to enhance query performance.

Index Type

Standard Use

primary index

To satisfy an equality on an IN condition in a join

unique primary index

To ensure the fastest access to single rows

non unique primary index

• To perform a single-disk row selection or join process
• To avoid sorting or redistributing rows

unique secondary index

For processing requests that employ equality constraints

unique primary indexes to match values in one table with index values in another

To ensure optimal join performance.

A composite index only

For optimal processing of requests that employ equality constraints for all fields that comprise the index. 

bitmapped NUSI

For processing requests when equality or range constraints involving multiple NUSIs are applied to very large tables.

For smaller tables, the Optimizer uses the index estimated to have the least amount of rows per index value.
Using appropriate secondary indexes for the table can increase retrieval performance, but the trade-off is that the update performance can decrease