Oracle SQL: Tuning tips

Oracle Tuning Tips
Watch Non-Indexed WHERE Conditions
Oracle evaluates Non-Indexed conditions linked by AND bottom up
Bad: select * from address where
                         areacode = 500003 and
                         type_nr = (select seq_nr from code_table where type = ‘HOME’)
Good: select * from address where
                         type_nr = (select seq_nr from code_table where type = ‘HOME’) and
                         areacode = 500003
Oracle evaluates Non-Indexed conditions linked by OR top down
Bad: select * from address where
                         type_nr = (select seq_nr from code_table where type = ‘HOME’) or
                         areacode = 500003
Good: select * from address where
                         areacode = 500003 or
                         type_nr = (select seq_nr from code_table where type = ‘HOME’)
Oracle Tuning Tips UNION/OR
Consider IN or UNION in place of OR  
i
 columns are not indexed, stick with OR
if columns are indexed, use IN or UNION in place of OR
IN example
Bad: select * from address where
                         state = 'AP‘ or
                         state = 'KL‘ or
                         state = 'KL‘
Good: select * from address where
                         state in ('AP','KL','KL')
UNION example
Bad: select * from address where
                         state = ‘KL’ or
                         areacode = 500003
Good: select * from address where
                         state = ‘KL’
               union
               select * from address where
                         areacode = 500003