Casting

Unlike some databases, Oracle allows queries to compare numbers to strings and strings to dates without warning or complaint. In doing so, it must internally perform a conversion - or cast - one of the expressions.

WHERE txn_date = '01-JUL-03'
Comparing a string to a date, the string is always cast to a date.
WHERE sales_qty = '25'
Comparing a string to a number, the string is always cast to a number.

In both of the above examples, it is the constant expression that is cast. These will be OK. The problem occurs when indexed columns or hash cluster keys are cast. If an indexed VARCHAR2 column is cast to either a date or a number, then the index is unusable. The same is true for VARCHAR2 hash cluster keys.

Consider the SQL:

SELECT *
FROM   sales
WHERE  product_code = 32

If product_code is indexed and a number, there is no problem. But if product_code is a VARCHAR2 (or CHAR), then it will be cast to a number and the index will be ignored.

The problem occurs most commonly with table joins:

SELECT s.employee, s.dept_num, c.description
FROM   sales s, generic_code_table c
WHERE  s.dept_num = c.code
AND    s.invoice_no = 1233

In this example, generic_code_table is a general purpose table for code and description type lookups. For simplicity, code is always a VARCHAR2, but is also used to handle numeric codes. The dept_num column in sales is numeric, so when the join is performed, code is cast to a number and the index is ignored.

Nothing will tell you that casting is occurring - not even Explain Plan - you have to work it out for yourself.

To correct the problem, you can:

Fix the query so that it doesn't cast. eg. Change product_code = 32 to product_code = '32'
Use to_char() to convert the date or numeric to a string, rather than having Oracle convert the string to a date or number. eg. change s.dept_num = c.code to to_char(s.dept_num) = c.code