All Forums Database
ayes 24 posts Joined 02/06
05 Sep 2007
what is cover index

Hi,Please explain the cover index?Regards,Ayes

joedsilva 505 posts Joined 07/05
05 Sep 2007

Teradata stores the values of the columns referred in the Index definition in the Index Subtable. Index subtables are generally smaller than data subtable. So if a query refers only to columns that are part of the index, TD would decide to read the Index subtable to return the information rather than going through the data subtable.The reason is that since Index subtable is smaller than data subtable, there are more records per block in the index subtable compared to a Data subtable. So it's more efficient to read information from the index subtable.Consider this to a library system. If you wanted to take a count of the number of titles in the Library, you can either go around the library counting the books in the shelves (data subtable) ... or if you are smart, you can borrow the catalog (index) from the librarian and just count the titles written on it... which one is faster ? (ok I assumed the catalog is always up to date and no body borrows books ;-) ... but you got the point)Similarly imagine you have a huge (lots of columns) employee table and you have an NUSI on deptno. if you did a SELECT COUNT(DISTINCT DEPTNO) FROM EMPLOYEE; TD can read the index subtable to satisfy that query (This is evident if you do an explain).

Jim Chapman 449 posts Joined 09/04
05 Sep 2007

While it is true that traversing a covered index may be faster than traversing the base table, I would think the more important value of a covered index comes into play when the index is used as an index, and the covered columns can be used to answer the query without accessing the base table. In that case, the query is executed as a single-AMP step. If the needed columns had not been in the index, then an additional multi-AMP step would have been needed to retrieve them.

You must sign in to leave a comment.