full table scan

My application developers approached me with an issue which is very unique to me. They were complaining about a query which was failing with ORA-01722 “invalid number” after an upgrade to 11.1.0.7 from 10.2.0.4. The syntax of the query is like:

select max(a) from t1 where c1<>'abc' and c2=12345 and c3='Y' and c4='xyz';

This query worked fine in 10204 and was also working fine in another, upgraded, 11.1.0.7 database.

All the columns i.e C1,C2,C3 & C4 are varchar 2(20) .

I ran this query with single quotes around column C2 as:

select max(a) from t1 where c1<>'abc' and c2='12345' and c3='Y' and c4='xyz';

and it worked fine but without single quotes it failed again with same error.

I checked the explain plan of the query and it was doing a “Full Table Scan” on Table T1. Then I opened another 11.1.0.7 database where the same query is working fine and found that there is an index on columns C1,C2,C3 & C4 and the table T1 was getting accessed by Index-Range scan.

Now coming back to the failing 11.1.0.7 database, index on column C4 was missing. After creating index on column C4 the query started to work fine at failing instance.

I am not sure how the absence of an index can cause this issue? Why VARCHAR2 cannot recognize a value without quotes when doing a Full Table Scan?

Your comments are always welcome. Please let us know your views on this.