Re: Force implicit data conversion
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- Date: Wed, 04 Mar 2015 20:22:14 +0000
Ok, I looked, and found the docs ...
11GR2 at
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements002.htm
12C at http://docs.oracle.com/database/121/SQLRF/sql_elements002.htm
And I quote ...
Implicit and Explicit Data Conversion
Oracle recommends that you specify explicit conversions, rather than rely on
implicit or automatic conversions, for these reasons:
· SQL statements are easier to understand when you use explicit data
type conversion functions.
·
· Implicit data type conversion can have a negative impact on
performance, especially if the data type of a column value is converted to that
of a constant rather than the other way around.
·
· Implicit conversion depends on the context in which it occurs and may
not work the same way in every case. For example, implicit conversion from a
datetime value to a VARCHAR2 value may return an unexpected year depending on
the value of the NLS_DATE_FORMAT parameter.
·
· Algorithms for implicit conversion are subject to change across
software releases and among Oracle products. Behavior of explicit conversions
is more predictable.
·
· If implicit data type conversion occurs in an index expression, then
Oracle Database might not use the index because it is defined for the
pre-conversion data type. This can have a negative impact on performance.
... (Ignore conversion table here) ...
· During INSERT and UPDATE operations, Oracle converts the value to the
data type of the affected column.
·
· During SELECT FROM operations, Oracle converts the data from the
column to the type of the target variable.
...
So, it looks like Oracle will convert the column's data to the literal's data
type, if necessary, and this is going to affect all the rows selected.
Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Other related posts:
