Oracle automatically create a B-tree Index for Primary key or the column which has UNIQUE constraint.
CREATE [UNIQUE] INDEX index_name ON
tab_name(col_name[, col_name ...])
Creating a Funtion-Based Index
CREATE INDEX i_name ON tab_name(UPPER(col_name));
note : need to enable the parameter QUERY_REWRITE_ENABLED (it is false by default)
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;
ALTER INDEX i_name RENAME TO i_name;
Good Candidate for Bitmap Index is a column that is referenced in many queries, but it contains only a small range of values;
If the number of distinct values of a column is less than 1 percent of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.
CREATE BITMAP INDEX i_name ON tab_name(col_name);