Intermediate SQL Color Coded SQL, UNIX and Database Essays

27Jan/100

How ORACLE Implicit Type Conversion Works. Part 2

Those of you who read my previous post would remember that ORACLE implicit type conversion approach
contradicts common sense, yet, you would probably agree, that there are some excellent reasons for ORACLE to go with it.

This post will explore implicit type conversion further and, in a way, turn everything you read about in part 1 upside down.

If the motto of the first part of this post was: Always question your common sense, the motto of the second part will be: Do NOT accept anything as dogma (even if your read it on this blog 🙂 )

I’m actually speaking from experience here as a while ago I was showing a group of developers the very unorthodox ways of ORACLE implicit type conversions and, to my shame, in the middle of the presentation found that under some circumstances ORACLE does not really “want to cooperate”, even when you try the experiment 3 times straight … Well, enough horror stories for today …

But back to the dogmas. Here is a good one from the previous post:

The dogma from Part 1 ...

In conditions: WHERE column = literal_of_different_type

ORACLE would ALWAYS convert column type to literal type

In other words, we believe that for multiple good reasons ORACLE would always transform this expression into:

WHERE to_literal_type(COLUMN) = literal_of_different_type

To show that this is not always the case, let’s change the example from part 1 slightly:

-- Let's create the same table as in example 1 but with number column
-- instead of character
SQL> CREATE TABLE t2 (id PRIMARY KEY) NOLOGGING
AS SELECT CAST(level AS NUMBER) FROM dual CONNECT BY level <= 100000;

TABLE created.

-- Collecting statistics ...
SQL> EXEC dbms_stats.gather_table_stats(USER, 't2', cascade => TRUE,
estimate_percent => NULL);

PL/SQL PROCEDURE successfully completed.

SQL> SET autotrace ON

-- Confirming that 'no conversion' scenario wors as expected ...
SQL> SELECT * FROM t2 WHERE id=25;

ID
----------
25

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2112715771

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00463489 |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

1 - access("ID"=25)

-- And now, move on to the implicit conversion …
-- Since COLUMN and LITERAL types are different
-- We should expect to_char() function to be applied to the ID column
-- And a full table scan as a result
SQL> SELECT * FROM t2 WHERE id='25';

ID
----------
25

Execution Plan
----------------------------------------------------------
Plan hash VALUE: 2112715771

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00463489 |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

1 - access("ID"=25)

Wow! What happened here? No function applied to the column and no full table scan…

ORACLE somehow magically recognized ’25’ as 25 and performed sensible implicit conversion to use the index.

How exactly did this happen ? Is there something special about numbers (as opposed to characters)?

Well, there is and here is a hint:

A Hint

ALL numbers ARE characters …
BUT
NOT ALL characters ARE numbers …

In other words, in a sense, CHARACTER is a “broader” data type than NUMBER as it might store not only “numbers” but more “kinds” of data.

Because of it, when comparing NUMBER column with a CHARACTER literal, ORACLE no longer needs to check data conformance or worry about possible misinterpretations of the question.

By the nature of the (very restrictive) NUMBER type, all the data in the column MUST CONFORM and, in addition to that,
it can be only stored in a single ‘approved’ fashion.

Hence, ORACLE can transform the expression efficiently (it does care about performance, after all) …

But what about other data types – is their implicit conversion ‘efficient’ (part-2-esque) or ‘unorthodox’ (part-1-esque) ?

As always, practice is the best teacher and this itc_test.zip short exercise will provide the answer, which I summarized in the table below:

COLUMNNUMBERFLOATDOUBLEDATETSTS_LTZTS_TZCHARRAW
NUMBERNATIVELEFTLEFTERRORERRORERRORERRORRIGHTERROR
FLOATRIGHTNATIVELEFTERRORERRORERRORERRORRIGHTERROR
DOUBLERIGHTRIGHTNATIVEERRORERRORERRORERRORRIGHTERROR
DATEERRORERRORERRORNATIVELEFTLEFTLEFTRIGHTERROR
TSERRORERRORERRORRIGHTNATIVERIGHTLEFTRIGHTERROR
TS_LTZERRORERRORERRORRIGHTRIGHTNATIVELEFTRIGHTERROR
TS_TZERRORERRORERRORRIGHTRIGHTRIGHTNATIVERIGHTERROR
CHARLEFTLEFTLEFTLEFTLEFTLEFTLEFTNATIVERIGHT
RAWERRORERRORERRORERRORERRORERRORERRORLEFTNATIVE

Legend:

NATIVENo conversion “Native” transformation when COLUMN type is the same as LITERAL
RIGHT“Light” conversion when LITERAL is converted to COLUMN type: COLUMN = to_column_type(LITERAL).
Regular indexes will still be usable here
LEFT“Heavy” conversion when COLUMN is converted to LITERAL type: to_literal_type(COLUMN) = LITERAL.
Regular indexes will likely become unusable in queries
ERRORExactly what it says – implicit conversion is NOT possible

And a few comments:

  • There is no difference between CHAR and VARCHAR2 – for the purpose of implicit conversion they are the same basic data type
  • There is no difference between NUMBER(5) and NUMBER(10) or VARCHAR2(1) and VARCHAR2(20)
  • As far as I can tell, these rules are the same for any ORACLE version starting from 9.2 up to 11.2
  • CHARACTER type seems to be the broadest of them all, only RAW is “broader” (but most of its implicit conversions are prohibited)
  • CHAR/VARCHAR2 and DATE columns seems to be the most “dangerous” performance wise as majority of their allowed conversions are LEFT. In practice, however, you need to mostly worry about CHAR columns
  • If you wonder how you can possibly compare DATE/TIMESTAMP column to CHAR literal and vice versa, you are not alone, but it is, of course, possible if NLS settings are “just right”

All in all, the general rule of implicit type conversion seems to be:

The General Rule

WHEN Narrow Type IS COMPARED TO Broad Type –> ORACLE IMPLICITLY CONVERTS TO Narrow Type

But don’t take my word for it … 🙂