1

I am studying about Projection index in a datawarehouse. According to the paper "Indexing Techniques for Data Warehouses Queries", by Vanichayobon,S. and Gruenwald,L., the Projection index on any column in a table, stores all the values in that column, in the exact same order as in the table. I didnt quite understand, how is it advantageous copying the SAME COLUMN VALUES in SAME ORDER to the index, as querying from the index would take same time as querying from the table (as per my understanding and assumption). Can anyone throw some light on this ?

1

1 Answer 1

1

As with other kinds of indexes, Projection Indexes are there to speed up certain specific queries and operations, (usually at the expense of slower inserts and updates but since this is for data warehousing, it shouldn't be much of a deal).

Grouping together all the values of a single column speeds up aggregate operations involving that column. For example, suppose you're summing up a column consisting of Int values, and the Page Size is 4KB. Each page read from the Projection Index will fetch 1000 values at a go. However, if every row of that table took up 100 bytes, then without the index, a single page read will only retrieve 40 values at a go.

Storing the values in the table order is also important for the index. Suppose the database is carrying out a SELECT ... WHERE query and so far, the query has been evaluated from other indexes rather than via a sequential scan. Furthermore, Row ID information is included in the result-set. Now, in the same query, you wanted to include the values for a specific column. If you'd already added a Projection Index for that column, then retrieving the values will be faster (for the same reason as before - fewer page reads) and since they were in table order, the column values will be included correctly based on the row ID.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.