Re: When to Create Index?

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

Because B*-tree indexes don't include NULLs, you can use an index to reach not-null values very fast, and your index will have a relatively small size.

>>But,

why it is 2-4% and not some other values?

Don't mind it. There are different points of view at this figure. Consider the following - usually, to reach a certain value by index you will have to read 4 blocks - 3 index blocks and 1 data block. Using full scan you need to read from 1 to n blocks - depends on where your data are. If the average number of blocks you have to read to find data exceeds 4 - you can think about the using of the index. For example in the case of scattered data the 8-blocks table is a candidate for using indexes. 1/8 is about 15%, what is mentioned as the effectiveness threshold in Oracle9i Application Developer's Guide - Fundamentals. Received on Thu Feb 24 2005 - 04:27:19 CST