Implicit type conversion in where clause

I spent a lot of time yesterday digging into performance issues on a new database only to find a simple issue where two tables were joined on a column that was a number on one table and a varchar2 on the other.  That column was a varchar2(4) on six or eight tables but one table – and it had to be the one with the most rows – had the same column defined as number(4) and as a result the queries were running with inefficient plans.  All I had to do to find this was get a plan of the sample query I was working on and look for TO_NUMBER in the predicates section but of course I spent hours looking at other things first.  So, I thought I would document how to make a quick check for this kind of type conversion.  It is “implicit” type conversion because there is no TO_NUMBER in the sql itself.  The optimizer has to add the TO_NUMBER so it can compare the character column to the number column.

Here is how I setup the example tables to mimic the situation I saw yesterday:

-- table with number column

create table ntest (div_nbr number(4));

-- table with character column

create table ctest (div_nbr varchar2(4));

Here is a sample query that joins these two tables on the columns of the same name but different types:

-- join on the column

select count(*)
from ntest,ctest
where ntest.div_nbr=ctest.div_nbr;

Here is how I get the plan which will includes the predicates section with the TO_NUMBER conversion function:

-- get plan

select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

Finally, here is the plan with the predicates section with the TO_NUMBER function:

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)|
|   0 | SELECT STATEMENT    |       |       |       |     5 (100)|
|   1 |  SORT AGGREGATE     |       |     1 |    17 |            |
|*  2 |   HASH JOIN         |       |     1 |    17 |     5  (20)|
|   3 |    TABLE ACCESS FULL| NTEST |     1 |    13 |     2   (0)|
|   4 |    TABLE ACCESS FULL| CTEST |     1 |     4 |     2   (0)|

Predicate Information (identified by operation id):

   2 - access("NTEST"."DIV_NBR"=TO_NUMBER("CTEST"."DIV_NBR"))

Step 2 of the plan is the hash join and the predicate information section shows how the two columns are joined for this step.  CTEST.DIV_NBR has to be converted to a number before it can be compared to NTEST.DIV_NBR.

So, I recommend putting a check for TO_NUMBER in the predicate information section of the plan into your toolkit of things to check when tuning a query.  This kind of thing shows up more often than you would think and it is hard to find because if you look at the query itself you just see a normal join on the DIV_NBR column.

Our resolution of this issue was to change the type of the one table to varchar2(4).  This combined with tuning optimizer_index_cost_adj resulted in dramatic improvements in performance on our test system.

Here is a zip of the script I used to show how to find TO_NUMBER in the predicate section.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply