Oracle Tuning:Sql Perfomance tuning Technics

Watch Indexed WHERE Conditions
Assume index on address (city, state)

Non-leading index column references cannot use indexes
Where state = 'AP'     [Index Not used]
Where city = 'NELLORE'    [Index Used]

Where state = 'AP' and city = 'NELLORE'   [Index Used]
NOT, != and <> disable index use
Where state not in ('AP', 'TN','UP'')   [Index Not used]
Where state! = 'AP'    [Index Not used]

NULL value references can never use indexes
Where state IS NULL    [Index Not used]
Where state IS NOT NULL    [Index Not used]

Expression references can never use indexes
Where substr(city,1,3) = 'NELLORE'    [Index Not used]
Where city like 'NELLORE%'    [Index Used]

Where city || state = 'NELLOREAP'   [Indx Not used]
Where city = 'DALLAS' and state = 'AP‘   [Index Used]

Where salary * 12 >= 24000    [Index Not used]
Where salary >= 2000    [Index Used