Avoid Implicit Data Conversion in WHERE Clause
What is an implicit conversion?
SQL is designed to be obliging. Although it is strictly typed, it tolerates a degree of carelessness in the SQL code it consumes. It allows such things as implicit conversion just so long as the database engine can work out what you want from your SQL Query. It doesn’t have to be good code; any reasonable RDBMS will execute it. But if it is a commonly executed query, then it will degrade the performance of your application.
Implicit conversions generally happen when, in a WHERE or FROM clause filter of a query, you specify a column with a datatype that doesn’t match the datatype of the column in the table. When two items of data with different data types are being compared in SQL, all the values with the lower-precedence datatype must be converted up to the higher precedence type before the comparison can proceed, and the filter applied.
A VARCHAR2 or CHAR value can be implicitly converted to NUMBER or DATE type value by Oracle. Similarly, a NUMBER or DATA type value can be automatically converted to character data by Oracle server. Note that the implicit interconversion happens only when the character represents the a valid number or date type value respectively.
For example, examine the below SELECT queries. Both the queries will give the same result because Oracle internally treats 15000 and '15000' as same.
SELECT employee_id,first_name,salary FROM employees WHERE salary > 15000;
SELECT employee_id,first_name,salary FROM employees WHERE salary > '15000';
SELECT employee_id,first_name,salary FROM employees WHERE hire_date > '2021-05-11';
In cases like this, the runtime engine automatically converts the string to a number and string to a date, using the default format mask.
You should, however, avoid implicit conversions in your code. There are at least two big problems with relying on PL/SQL to convert data on your behalf:
Conversion behavior can be non-intuitive. PL/SQL may convert data in ways that you don’t expect, resulting in problems, especially within SQL statements.
Conversion rules aren’t under the control of the developer. These rules can change with an upgrade to a new version of Oracle or by changing RDBMS-wide parameters, such as NLS_DATE_FORMAT.
For number to character conversion, nls parameters can be used to specify decimal characters, group separator, local currency model, or international currency model. It is an optional specification - if not available, session level nls settings will be used. For date to character conversion, the nls parameter can be used to specify the day and month names, as applicable.
You can convert explicitly using any of the following built-in functions: TO_DATE, TO_CHAR, TO_NUMBER, and CAST. Format models are case sensitive and must be enclosed within single quotes.
Consider the below SELECT query. The query format the HIRE_DATE and SALARY columns of EMPLOYEES table using TO_CHAR and TO_DATE function.
SELECT first_name, FROM employees WHERE hire_date > TO_DATE('2021-05-11', 'YY-MM-DD') AND Salary = TO_CHAR (salary, '$99999.99') ;