Hi Tom,
My question is regarding when a query is not having the right datatype in the where clause
Example:
-- Create table
CREATE TABLE mytable (
mynumber varchar2(20), primary key(mynumber));
-- Insert some rows
insert into mytable (mynumber)
select rownum from dual
connect by rownum <100
;
commit;
-- Query with right data type
select * from mytable where mynumber = '10';
-- 1. Explain plan
-- Notice use of index with unique scan
-- 2. Execute query
-- > 10
-- Query with wrong data type
select * from mytable where mynumber = 10;
-- 1. Explain plan
-- Notice to_number and full scan on index
-- 2. Execute query
-- > 10
-- Insert new row with value that cannot be converted to number
insert into mytable (mynumber) values ('xyz');
commit;
-- Query with right data type
select * from mytable where mynumber = '10';
-- 1. Explain plan
-- Notice use of index
-- 2. Execute query
-- > 10
-- Query with wrong data type
select * from mytable where mynumber = 10;
-- 1. Explain plan
-- Notice to_number and full scan on index
-- 2. Execute query
-- > ORA-01722: invalid number
I am able to understand what's going on, but my question is why Oracle chooses to convert all rows (index) to number instead of converting the value in the where clause. I expect there is a perfectly good explanation for that, but I can't seem to figure it out.
BR
Jens
Think about the following values:
01
1
1.0000
Numerically they're all the same value: one.
But if you compare them as text they're all different!
So when comparing numeric values to strings, the database will to_number the text. This ensures you get all the rows with the same numeric value:
create table t (
c1 varchar2(10)
);
insert into t values ( '01' );
insert into t values ( '1' );
insert into t values ( '1.000' );
select * from t
where to_number ( c1 ) = 1;
C1
01
1
1.000
select * from t
where c1 = to_char ( 1 );
C1
1
The conversion will happen on whichever expression returns character values. Flip the data types around and you'll get all the rows back:
drop table t cascade constraints purge;
create table t (
c1 number
);
insert into t values ( '01' );
insert into t values ( '1' );
insert into t values ( '1.000' );
select * from t
where c1 = '1.0';
C1
1
1
1