Create Index and using Index hints

CREATE INDEX:

There are many types of index introduced by Oracle.
http://docs.oracle.com/cd/B19306_01/server.102/b14231/indexes.htm

Each type have the different way to create. Ex:

CREATE INDEX programId_index ON PROGRAM(programId) COMPUTE STATISTICS;

Put Index on columns that appear in WHERE statement.
Good for single column - one index.
Can put multi-columns in index but make sure that the all these columns appear in WHERE statement. If not, index does not help.

Ex: CREATE INDEX User_index ON PROGRAM(firstName, lastName, title) COMPUTE STATISTICS;

Great if: SELECT * from User WHERE firstName = ? AND lastName = ?

Bad if: SELECT * from User WHERE lastName = ?
       or SELECT * from User WHERE lastName = ? AND title = ?

Index will make DML (insert, update, delete) slow down (3-time slower than the table with no indexes). Should not index on column that value is too much duplicated.

INDEX HINTS:

Use Hints indicate Oracle using index:

SELECT /*+ index(table_name(column_has_indexed)) */  * FROM customer;