Question

4. (15 points) Consider a situation in which reports are often generated for each value of a column with only a "few" possible values, (such as a "M F" gender column or a "State" column (a) Are these columns usually good candidates for indexes? Why or why not? gender column or a State column (b) What if the tuples in these tables could be stored on disk in order (lexically, numerically, whatever is appropriate) would queries benefit from this? Why or why not?

Answer 1

(a) Indexes will be useful if:

• The column or columns have a high degree of uniqueness
• You frequently need to look for a certain value or range of values for the column.

They will not be useful if:

• You are selecting a large % (>10-20%) of the rows in the table
• The additional space usage is an issue
• You want to maximize insert performance. Every index on a table reduces insert and update performance because they must be updated each time the data changes.

Hence in our case, we are generating reports from Gender Column (which consists of two values, Male and Female) and State column, of which only a few values are selected for our querying purpose. These columns either dont have a high degree of randomness (eg Gender) or dont have any range of values (ie State). So, it will not be useful to create indexes on "Gender" and "State" columns.

(b) Even if we store these tuples of a given Table on a disk, queries will not benefit us as filtering out the data (ie. based on Gender or State wise) will be too difficult to perform for a Disk based system and thus report generation will become much harder. If we store them in Database we can filter out the reports based on WHERE Clause and select only those data sets that we want to view as a part of the report.

PLease let me know in case of any clarifications required. Thanks!