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 ...view middle of the document...
• 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 FISCAL_MONTH as the high-order participant.
Does the column support a primary key?
• Oracle almost always creates an index to support a primary key. If no unique index exists for the primary key, then create one!
Does the column support a foreign key?
• Unless the child table contains very few rows, it is a good idea to have indexes on foreign keys.
Does the column support a unique constraint?
• Again, Oracle might do this automatically. But if Oracle does not automatically create an index to enforce a unique column constraint, then do so!
Composite Indexes vs. Single Column Indexes
• In only rare cases, a single column index should be used.
• In most cases, a multi column or composite index will give you more flexibility and more functionality than a single column index.
• Oracle will follow a highest order rule of usingn index columns. For example, if UID is the high order of a composite index, and you give only UID in the where clause, Oracle will gladly and efficiently use that index.
• Similary, I think Oracle will use as much of the index as it can to avoid going to the table if it can. For example, if your composit index is UID, ZIP, and LNAME, and you specify UID and LNAME in the where clause, Oracle can satisfy the where clause strictly by using the index. This is not always guaranteed, but it's a good bet.