CS614 Vu current GDB 4th August 2014

Consider the following table structure:

ProductID (Primary key, Integer, Not NULL)

ProductName (String, Not NULL)

ManufacturingDate (Date, Not NULL)

ExpiryDate (Date, Not NULL)

Category (Integer, Not NULL, Refers to Category column in Category table)

UnitPrice (Float, Not NULL)

Question: Suppose we want to create index (primary) on this table. Identify the indexing technique, you think is best to consider in this case. Justify your answer with valid reasons.

Note: Try to provide precise, to the point answer and avoid irrelevant details.

Solution  : 

1. Low cardinality – Some dabase vendor, like Oracle, provides very practical suggestion -

• If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.
• B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER.
• There are 100 or more rows for each distinct value in the indexed column. When this limit is met, the bitmap index will be much smaller than a regular index, and you will be able to create the index much faster than a regular index.

2. No or little insert/update -
Updating bitmap indexes take a lot of resources. Here are the suggestions:

• Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity. Designers must ensure that the benefits of any index outweigh the negatives of index maintenance.
• Use this simple estimation guide for the cost of index maintenance: each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERTinto a table with three indexes, then it will be approximately 10 times slower than an INSERTinto a table with no indexes. For DML, and particularly for INSERT-heavy applications, the index design should be seriously reviewed, which might require a compromise between the query and INSERT performance.

3. Multiple Columns
One of the advantage is that multiple bitmap indexes can be merged and the column does not have to selective!

• More than one column in the table has an index that the optimizer can use to improve performance on a table scan. 
• Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O.