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.

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:

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:

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:

©Copyright 2003