Tuesday, February 15, 2011

How does indexing improve query performance?

 How does indexing improve query performance?
Answers:

Indexing is a way to physically reorganize 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 it back to the user.
Or
The frequently 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 maintenance. 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.

No comments:

Post a Comment