Implicit type conversion guauantee proper execution plan when using character type value with numeric type column in Oracle
Explanation
Oracle database has lots of implicit type conversion rules and one of them goes like that when comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
This is very important considering execution plan and the performance of query.
For example, with the following query where price column is varchar type.
When executing the above query, Oracle database converts the query like the following and so, there's no problem for the query to use the index on price column. In other words, the above query using character type value with numeric type column doesn't hurt the execution plan of the query and this can be generalized.
Sample
The following is sample table and script showing the above explanation.
Table
Script
DML script to execute query and identify the execution plan used.
Result
The execution of the above script goes like this
0 comments:
Post a Comment