Implicit type conversion guauantee proper execution plan when using character type value with numeric type column in Oracle
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
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.
The following is sample table and script showing the above explanation.
DML script to execute query and identify the execution plan used.
The execution of the above script goes like this