When to Create Index?

In some Oracle's training material, it lists the condition to create index as follows:

1. A column contains a wide range of values
2. A column contains a large number of null values
3. 1 or more columns are frequently used inWHERE clause
4. The table is large and most queries are expected to retrieve less than 2 to 4 % of the rows.

I understand the rationale behind point 1, 3 only.

For point 2, what do NULL values have to do with index?

For point 4, I guess it is about benefit of index versus full table scan - if the queries return a lot of rows, full table scan isnt a bad idea. But, why it is 2-4% and not some other values? Received on Thu Feb 24 2005 - 01:04:30 CST