CODEWITHSUNDEEP
teradata
Vivek Harry
Vivek Harry

Reputation: 439

Indexes in Teradata

How to Enable/Disable Indexes in TERADATA Database?

I want to disable indexes and do update and then enable the indexes in Teradata.

If Enable/Disable option not available in Teradata, in the sense How can I achieve this ? If I use DROP Indexes, how can I recreate the indexes for all the tables?

Upvotes: 2

Views: 10011

Answers (6)

Clark Perucho
Clark Perucho

Reputation: 466

Since you did not specify what kind of index you want to disable/enable, below are the approach you can follow on either cases.

  • PRIMARY INDEX
    1. CREATE a new table with the same PI
    2. INSERT the updated data to new table
    3. DROP the old table - DROP TABLE <OldTable>;
    4. RENAME the new table same as the old one. - RENAME TABLE <NewTable> TO <OldTable>;

Above recommendation for Primary Index is only applicable if you are going to update a primary index column value. If you will update other columns (not the PI column) then you can just issue an UPDATE Statement directly.

  • SECONDARY INDEX
    1. DROP the SI - DROP INDEX <IndexName> ON <TableName>;
    2. UPDATE table data
    3. RECREATE the SI - CREATE INDEX <IndexName> (<ColumnList>) ON <TableName>;

Upvotes: 0

Alex
Alex

Reputation: 741

Simple answer - you can't disable and re-enable indexes in Teradata. However, there some workarounds.

Drop Index If you talking about PI (primary index) - you can't drop it. All you can do is to make a copy of a table without an index. You can drop secondary index though. Then simply create it again when you need it.

Drop-Create Table This doesn't fit all cases, but often this is the fastest way to do the work, especially if the issue you have is with PI.

BTW: it is not clear, why would you need to do that? Performance or logic or something else? That probably will affect recommendation.

Upvotes: 1

v83rahul
v83rahul

Reputation: 313

Teradata gives you a way to create a table without choosing for primary index (if you are sure of any column).

You can create table with No primary Index. Here is a sample of how to do so:

Create table <table name> 
(<columnname> <datatype>,
<columnname> <datatype>) 
no primary index ;

Upvotes: 3

k.asish kumar patro
k.asish kumar patro

Reputation: 21

In Teradata you can't drop Primary index of a table. The primary index defines where data will reside and which AMP receives the row.

To alter the primary index of a table you need to delete all the records from the table ( As data is already distributed by the row hash value of the PI) then only you can change the primary index of a table by using below command:-

Alter table table_name modify primary index index_name (column list);

Steps to achieve your goal

  • You can crate a new table with your desired index ( temp, wrk, intermediate table) insert the records from the original table and update the wrk table.
  • delete the original table and insert the wrk table data.

And you are done.

Upvotes: 1

Shrikant Soni
Shrikant Soni

Reputation: 2327

Create & Drop index is the only option you have here

Upvotes: 0

Dennis
Dennis

Reputation: 4017

Teradata does not have a disable index feature.

All tables have a Primary Index (PI) which is chosen by the RDBMS unless you specify one. 

CREATE INDEX <index name> (<column list>) ON table name;

CREATE UNIQUE INDEX (department) ON tbl_employee;

DROP INDEX ind_dept ON tbl_employee;

DROP INDEX (department,emp_number) ON tbl_employee;

Upvotes: 2

Related Questions