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.
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:
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.
Please send me comments or suggestions: firstname.lastname@example.org
Take a look at my HomePage