Oracle SQL: the Essential Reference

1.5. Data Conversion

There are multiple ways to represent data in a database. For example, a salary, which is normally considered a numeric value such as 25,000 can be represented easily as a character string such as "25000". Likewise, an employee ID can be represented as a number (500) or a string ("500"). If you attempt to perform an arithmetic operation on a character value in most computer languages, an error will occur. Not so with SQL. Oracle automatically performs a data conversion when it is necessary (and possible) to complete a requested operation. In the following SQL statement, assume that sal is defined in the database as CHAR(6)—a character string with a fixed length of six bytes:

SELECT ename, SAL * 1.1
FROM scott.emp;

The character string (sal) is multiplied by a numeric constant (1.1). To perform this operation, Oracle first converts the string into a number, and then performs the multiplication. This type of automatic conversion is an implicit data conversion.

While convenient, implicit data conversion also has a cost in CPU utilization, so be careful when deciding how to store data elements in the database. Implicit data conversion can have an unexpected negative impact on performance, since it can dramatically affect the way the Oracle query optimizer generates an execution plan. For example, use of an index may be suppressed due to implicit data conversion, ...