Case insensitivity in ORACLE
Just found out (3 years too late) Oracle supports functional keys. No other DB seems to do this (certainly not SQLServer or MySQL), but its quite handy. If you do oracle stuff and you have say a
Select * from tab where Upper(key) = 'HELLO'
You can speed this up *a lot* with:
create index IX_Index on tab (Upper(key));
Especially if the table gets big. You can then get ORACLE to perform more like SQL server with case insensitive searching.
Also worth noting ORACLE likes to load dlls and unload them on every connection creation. If your code does this, try and wrap up the selects into a single connection. It can be a big performance killer. Also look out for "Disable MTS" in the control panel. This aggravates the problem.
I still feel that any DB that needs this much work to make it good needs sorting out, but those two tips have sped up some code I have from over 12 hrs to 12 mins, since the bigger the table the more the performance hit!
Select * from tab where Upper(key) = 'HELLO'
You can speed this up *a lot* with:
create index IX_Index on tab (Upper(key));
Especially if the table gets big. You can then get ORACLE to perform more like SQL server with case insensitive searching.
Also worth noting ORACLE likes to load dlls and unload them on every connection creation. If your code does this, try and wrap up the selects into a single connection. It can be a big performance killer. Also look out for "Disable MTS" in the control panel. This aggravates the problem.
I still feel that any DB that needs this much work to make it good needs sorting out, but those two tips have sped up some code I have from over 12 hrs to 12 mins, since the bigger the table the more the performance hit!
Comments