How to Create or Drop the Secondary Index in Teradata
Secondary index in Teradata
Secondary index is an alternate path to the rows of a table in Teradata. It is used to
improve performance by allowing the user to avoid scanning the entire table during a query.Unlike a primary index, it has no influence on the way rows are distributed among AMPs. It is used to avoid the full table scan while accessing the data in the table.
- Secondary index can be created after the table is created.
- It may be dropped at any time.
- It does not affect table row distribution.It is chosen to improve the access performance.
- It can defined as Unique Secondary Index (USI) or Non Unique Secondary Index(NUSI)
Syntax to create the Secondary index in Teradata
|
1 2 3 |
CREATE INDEX <Index_Name> (Column_name) ON DB_Name.Table_Name; (or) CREATE INDEX (Column_name) ON DB_Name.Table_Name; |
Example to Create the Secondary index in Teradata
Lets create the table with few primary index values and then will create secondary index for the same table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE MULTISET TABLE Banking_DB.Customer_Txn ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( cust_id CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Transaction_date DATE FORMAT 'YYYY/MM/DD', Transaction_amount DECIMAL(38), Location_id CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( cust_id ,Transaction_date ) |
Create Non Unique Secondary index in Teradata
The customer transaction table is created with the primary indexes of customer id and Transaction date columns. If the data volume is high in the table, It will affect the query performance. In order to improve performance, we can create the secondary index for the Location id column.
|
1 2 |
CREATE INDEX Customer_Loc_IDX (Location_id) ON Banking_DB.Customer_Txn; |
We named the secondary index as Customer_Loc_INDX for the column Location_id. Since we didn’t mention the “UNIQUE” in the index creation, It will treated as Non Unique secondary Index.
Create Unique Secondary index in Teradata
In this example, we are going to create the Unique secondary index for the customer table. The column Location_id is used for the secondary index and Secondary index is named as “Customer_Loc_IDX” as below.
|
1 2 |
CREATE UNIQUE INDEX Customer_Loc_IDX (Location_id) ON Banking_DB.Customer_Txn; |
Lets run the Show table statement on this table to see the table structure.
|
1 |
SHOW TABLE Banking_DB.Customer_Txn; |
The below create table statement shows the Secondary index name “Customer_Loc_IDX” with the column name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE MULTISET TABLE Banking_DB.Customer_Txn ,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( cust_id CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Transaction_date DATE FORMAT 'YYYY/MM/DD', Transaction_amount DECIMAL(38,0), Location_id CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( cust_id ,Transaction_date ) -- Secondary index created for the column Location_id and named as Customer_Loc_IDX INDEX Customer_Loc_IDX ( Location_id ); |
Drop the Secondary index in Teradata
We can drop the secondary index at any time if it is not required. Either we can use the index name or mention the column name to drop it from the table.Lets see the syntax to drop the secondary index in Teradata.
|
1 2 3 |
DROP INDEX <Index_Name> on DB_Name.Table_Name; (or) DROP INDEX (Secondary_Index_Column_name) on DB_Name.Table_Name; |
Example to drop the secondary index in Teradata
|
1 |
DROP INDEX Customer_Loc_IDX on Banking_DB.Customer_Txn; |
- If we create the secondary index with the name, we can mention the index name to drop it from the table.
- If we didn’t specify the secondary index name, we need to mention the secondary index column name to drop it from the table.
Lets see the example to drop the secondary index which doesn’t have name.
|
1 |
DROP INDEX (Location_id) on Banking_DB.Customer_Txn; |
Recommended Articles
