If the statement is designed poorly, nothing much can be done by optimizer or indexes
Few known thumb rules
–Avoid Cartesian joins
–Use UNION ALL instead of UNION – if possible
–Use EXIST clause instead of IN – (Wherever appropiate)
–Use order by when you really require it – Its very costly
–When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
–Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0
– Avoid writing where is not null. nulls can prevent the optimizer from using an index
– Avoid calculations on indexed columns. Write WHERE amount > 26000/3 instead of WHERE approved_amt/3 > 26000
– The query below will return any record where bmm_code = cORE, Core, CORE, COre, etc.
select appl_appl_id where upper(bmm_code) LIKE ‘CORE%’
But this query can be very inefficient as it results in a full table scan. It cannot make use of the index on bmm_code.
Instead, write it like this:
select appl_appl_id from nci_appl_elements_t where (bmm_code like ‘C%’ or bmm_code like ‘c%’) and upper(bmm_code) LIKE ‘CORE%’
This results in Index Range Scan.
You can also make this more efficient by using 2 characters instead of just one:
where ((bmm_code like ‘CO%’ or bmm_code like ‘Co%’ or bmm_code like ‘cO%’ or bmm_code like ‘co%’) and upper(bmm_code) LIKE ‘CORE%’)