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
