what is cover index


what is cover index


Please explain the cover index?



Re: what is cover index

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).

Re: what is cover index

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.