Evaluate Index Design and Efficacy

How much data is there?

• If the entire table can be read into memory in a single I/O, then there is no need for indexing. To determine this, you have to know the DB_BLOCK_SIZE parameter in Oracle, plus the block size parameter for your OS. Then you get the number of blocks from dba_tables and determine whether or not the entire table will be fetched in a single I/O request.

• Or, you just make your best guess based on number of rows and row size.

What is the cardinality of the column you want to index?

• How to determine cardinality:

select col_name, count(*) from table_name group by col_name;

or, query dba_tab_columns

• If Oracle determines that using the index will return more than 10% of the table, then Oracle may decide instead to use a table scan.

• For low cardinality columns, consider using a bit-map index.

Is the column used in a where clause?

• With some minor exceptions, Oracle will only evaluate items in the where clause when looking for an index to use.

Is the column used in a summing function?

• A handy trick -- when you are summing a column based on some criteria in the where clause, and thecolumns in the where clause are already supported by an index, then consider adding the summed column to the low-order position of the index -- Oracle will gladly sum the values from the index without ever having to go to the table itself.

• Exception to this trick -- if Oracle has to go to the table to get still more values, then you really don't save anything.

Is the column always used in conjunction with another column?

• For example, if you have a column FISCAL_MONTH and the column FISCAL_YEAR, you have to ask why you would index only FISCAL_MONTH, when that item is more likely queried in conjuction with FISCAL_YEAR.

• Exception: if you have an application or report that looks at FISCAL_MONTH across FISCAL YEARS. In that case, of course, you'd still want both columns in the index (as well as whatever you're summing, perhaps), but with...