Automatic type casting won’t utilize INDEX

SQL is a strongly typed language:
columns are defined as a certain data type, and an attempt to enter a value of a different data type will usually fail. However, you may get away with this because
Oracle’s implementation of SQL will, in some circumstances, do automatic type casting.

An example below is a typical case when Automatic Type Casting feature was used. The value passed in the WHERE clause is a string, ’21-APR-08′, but the
column HIRE_DATE is not defined in the table as a string, it is defined as a date. To execute the statement, the database had to work out what the user really meant and
cast the string as a date.

Developers should never rely on automatic type casting. It is extremely lazy programming. They should always do whatever explicit type casting is necessary,
using appropriate functions such as TO_DATE(). TO_STRING() and TO_NUMBER() because there are many reasons for that.

One of them is indexing. INDEX will not be utilized when Automatic Type Casting occurs. In our example above, if there is an index on the HIRE_DATE column, it will be an index
of dates; there is no way the database can use it when you pass a string. So by using Automatic Type Casting feature, you are losing on performance on columns which have INDEX.

Therefore, this same query will work much faster, should conversion function be used.

As you can see execution time was 0.005 seconds this time, unlike the previous query which took 0.093 seconds for execution.

The second query was faster 18.6 times because Oracle did not spend time on Automatic Type Casting. This performance improvement was achieved simply because we did not use Automatic Type Casting Oracle feature.  The field HIRE_DATE was not even indexed ( see below )

Now, let’s have INDEX created on employees.HIRE_DATE field

Now let’s verify if INDEX was created

Now, let’s execute this same query again

As you can see this time execution time is 0.002 seconds. It is a serious gain if compared with 0.005 seconds from the previous query. Overall performance execution improvement is 46.5 times

Conclusion: 1) Don’t rely on Automatic Type Casting feature, it force database to fo extra work and therefore your query may execute 18.6 times longer ( like in our example ); 2) In order to improve performance even further use INDEX on the searched field. It may speed up your total execution up to 46.5 times. But you can’t use Automatic Type Casting if you have INDEX. I mean you can, SQL won’t return any error, but INDEX won’t be utilised and therefore performance gain in 46.5 times will be missed.

One thought on “Automatic type casting won’t utilize INDEX”

Leave a comment

Your email address will not be published. Required fields are marked *