Tuesday 25 February 2014

How does indexing improve query performance?

How does indexing improve query performance?

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
.

No comments:

Post a Comment