Re: Query anomaly...

Heikki.Karhunen_at_kone.com wrote in message news:<uk7x1el9l.fsf_at_kone.com>...
> Good morning, all...
>
> We have two Oracle database instances, one for development (DEV) and one
> for production (PRD). Now the schemas of these two database instances
> are as identical as I can see, but we seem to have managed to find an
> interesting query anomaly in them.
>
> The OS platform where these instances run is HP-UX 11.00, and the
> version of the Oracle is:
>
> Oracle8i Enterprise Edition Release 8.1.5.0.0, 64 bit - Producti
> PL/SQL Release 8.1.5.0.0 - Production
> CORE Version 8.1.3.0.0 - Production
> TNS for HPUX: Version 8.1.5.0.0 - Production
> NLSRTL Version 3.4.0.0.0 - Production
>
> We have in the schema a table CUSTOMER, the definition of which is:
>
> CREATE TABLE CUSTOMER (
> CUSTOMER_ID VARCHAR2 (16) NOT NULL,
> ...
> )
>
> This table definition is identical on DEV and PRD instances.
>
> At some time in the past the application that uses this database was of
> the opinion, that the CUSTOMER_ID field only contained numbers. Hidden
> somewhere in the depths of the application there was found the following
> query:
>
> SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = 12345 ;
>
> Now remember, that the type of the field CUSTOMER_ID is VARCHAR2, not a
> NUMBER (it was, but that is no longer the case). This kind of a query
> *should* always result in a nice little error message from the Oracle,
> like:
>
> ORA-01722: invalid number
>
> But! On the DEV database instance the Oracle does not complain at all,
> it just acts as if the number 12345 was enclosed in single quotes. But
> on the PRD database instance I do indeed get the error message.
>
> Could someone explain this behaviour to me in great detail as it is
> stretching my credibility a bit too far to actually believe that these
> two instances could behave this dissimilarily? Is there a way to get the
> Oracle database server to believe that single quotes are not a necessary
> requirement for query restrictions involving numbers?
>
> Best regards,
>
> HTK
You went to great pains to show that the database definitions are the same in both databases, but what about the data? I suspect it is not the same. All it takes is one entry in the production instance to trigger the error. I suspect that is the most likely source.

There is another possibility if the data is the same (slim but I think possible). Are there indices on the tables, especially this column? Have the tables been analyzed? What I'm thinking here is: The DEV database does not get updated, so the optimizer is using the index and finding the matching row without hitting the non-numeric values. The PRD instance is updated frequently (it is production after all) and the optimizer decided it needed to do a full table scan, running into the non-numeric on the way to the target value.

My bet would be the data differs.

Ed Prochak

Are the Received on Thu Nov 08 2001 - 09:09:14 CST