Explain for Function Based Index

[ligang]

CBO
create index test on bb(substr(dm,1,7));
create index test1 on bb(upper(dm));
-------------------------------------
1.select * from bb where substr(dm,1,7)=' 1350300' ;
2.select * from bb where upper(dm)='13503000000';

while executing 1st sql , the index test is not used but for the 2nd sql , the index test1 is choosed to be used.

Any special reason?

[abhaysk]

Originally posted by ligang
CBO
create index test on bb(substr(dm,1,7));
create index test1 on bb(upper(dm));
-------------------------------------
1.select * from bb where substr(dm,1,7)=' 1350300' ;
2.select * from bb where upper(dm)='13503000000';

while executing 1st sql , the index test is not used but for the 2nd sql , the index test1 is choosed to be used.

Any special reason?

cud be due N number of reasons...
But any ways Remove the space in ur 1st qry or make it 7 char'd constant val and see...