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'
WHERE sales_qty = '25'
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 = 32If 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 = 1233In this example,
generic_code_tableis a general purpose table for code and description type lookups. For simplicity,
codeis always a VARCHAR2, but is also used to handle numeric codes. The
salesis numeric, so when the join is performed,
codeis 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:
product_code = 32to
product_code = '32'
s.dept_num = c.codeto
to_char(s.dept_num) = c.code