Submitted by: Submitted by travelingdude
Views: 218
Words: 590
Pages: 3
Category: Other Topics
Date Submitted: 11/01/2011 09:33 PM
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...