I want to share a neat little trick to issue case insensitive queries and still use the index on the column being case "desensitized". Note: An alternative to this technique is to make use of function based indexes available in 8i.

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 a 2 concatenated table accesses by 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%')
This results in a 4 concatenated table accesses by Index Range Scan.