Confusion regarding to_char and to_number

user_9893677 0
Viewed 193

First of all, I am aware about basics.

select to_number('A231') from dual; --this will not work but

select to_char('123') from dual;-- this will work

select to_number('123') from dual;-- this will also work

Actually in my package, we have 2 tables A(X number) and B(Y varchar) There are many columns but we are worried about only X and Y. X contains values only numeric like 123,456 etc but Y contains some string and some number for eg '123','HR123','Hello'. We have to join these 2 tables. its legacy application so we are not able to change tables and columns. Till this time below condition was working properly

to_char(A.x)=B.y;

But since there is index on Y, performance team suggested us to do A.x=to_number(B.y); it is running in dev env.

My question is, in any circumstances will this query give error? if it picks '123' definitely it will give 123. but if it picks 'AB123' then it will fail. can it fail? can it pick 'AB123' even when it is getting joined with other table.

oracle12c oracle11g oracle
2 Answers

can it fail?

Yes. It must put every row through TO_NUMBER before it can check whether or not it meets the filter condition. Therefore, if you have any one row where it will fail then it will always fail.

From Oracle 12.2 (since you tagged Oracle 12) you can use:

SELECT *
FROM   A
       INNER JOIN B
       ON (A.x = TO_NUMBER(B.y DEFAULT NULL ON CONVERSION ERROR))

Alternatively, put an index on TO_CHAR(A.x) and use your original query:

SELECT *
FROM   A
       INNER JOIN B
       ON (TO_CHAR(A.x) = B.y)

Also note: Having an index on B.y does not mean that the index will be used. If you are filtering on TO_NUMBER(B.y) (with or without the default on conversion error) then you would need a function-based index on the function TO_NUMBER(B.Y) that you are using. You should profile the queries and check the explain plans to see whether there is any improvement or change in use of indexes.

user_15092640

Never convert a VARCHAR2 column that can contain non-mumeric strings to_number.

This can partially work, but will eventuelly definitively fail.

Small Example

create table a as
select rownum X from dual connect by level <= 10;

create table b as
select to_char(rownum) Y from dual connect by level <= 10
union all
select 'Hello' from dual;

This could work (as you limit the rows, so that the conversion works; if you are lucky and Oracle chooses the right execution plan; which is probable, but not guarantied;)

select * 
from a
join b on A.x=to_number(B.y)
where B.y =  '1';

But this will fail

select * 
from a
join b on A.x=to_number(B.y)

ORA-01722: invalid number

Performance

But since there is index on Y, performance team suggested us to do A.x=to_number(B.y);

You should chalange the team, as if you use a function on a column (to_number(B.y)) index can't be used.

On the contrary, your original query can perfectly use the following indexes:

create index b_y on b(y);
create index a_x on a(x);

Query

select * 
from a
join b on to_char(A.x)=B.y
where A.x = 1;

Execution Plan

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS     |      |     1 |     5 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| A_X  |     1 |     3 |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| B_Y  |     1 |     2 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."X"=1)
   3 - access("B"."Y"=TO_CHAR("A"."X"))
user_48081220
Related