Confusion regarding to_char and to_number

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.

Solution

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.