Implicit vs. explicit data conversion - impact on performance etc?
HaakonApr 6 2009 — edited Apr 7 2009When moving from 10g to 11g, we suddenly experienced problem with a simple SELECT statement that relied on implicit conversion from varchar2 to number. I.e. the WHERE clause compared a varchar2 field with an exact number without quotation marks. This used to work fine on our 10g database, on 11g it bombed out.
In itself a simple thing to fix, - but we have thousands of Warehouse Builder queries which relies heavily on implicit conversion (a bad thing - we know).
So I've several questions I'm trying to find answers to:
- how does one "turn on" implicit conversion in 11g, so that the database works like 10g in this regard?
- will implicit conversion cause slower execution times?
- will it break efficient use of indexes when the datatypes differ from the defined index?
Any input on this would be highly appreciated.
(In the mean time, we're going through some thousands of queries rewriting with TO_CHAR etc....)
Regards,
-Haakon-