RE: Storing single numbers in the database

Sandy,

   I once spent several hours trying to figure out why some code was
doing a full table scan even though there was an index on the column.

  

 

Table   BOB

            X  varchar2(1)

 

Create index idx_bob on x;

 

Select * from BOB where X = 1;

 

Even hinting did not make it use the index.  

 

Eventually the clue bulb lit up for me.  '1' is not equal to 1.   An
implicit conversion was being done in the database so the datatypes
would match.  Somehow a column that was ALWAYS a number had been defined
as varchar and wiped out access plans.

 

 

Chris

 

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandra Becker
Sent: Tuesday, June 05, 2007 3:05 PM
To: Baumgartel, Paul
Cc: oracle-l
Subject: Re: Storing single numbers in the database

 

Paul,

 

The developer believes that numbers are just characters, same as
letters, ergo they are treated the same way in the database.  I know
that the values are stored with different types, but beyond that, I
don't know what issues could bite me.  I have seen code for other
columns defined as CHAR or VARCHAR2 that use <, >, and <> to pull the
desired rows.  Would this be relevant to the discussion as well? 

 

Sandy

 

        The developer doesn't want to translate?  What, he thinks 0 and
1 are letters?

         


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________



NOTICE OF CONFIDENTIALITY: Information included in and/or attached to this 
electronic mail transmission may be confidential. This electronic mail 
transmission is intended for the addressee(s) only. Any unauthorized 
disclosure, reproduction, or distribution of, and/or any unauthorized action 
taken in reliance on the information in this electronic mail is prohibited. If 
you believe that you have received this electronic mail transmission in error, 
please notify the sender by reply transmission, or contact 
helpdesk@xxxxxxxxxxxxx, and delete the message without copying or disclosing 
it. 

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________

Other related posts: