RE: Storing single numbers in the database
- From: "Boyle, Christopher" <Christopher.Boyle@xxxxxxxxxxxxx>
- To: <sbecker6925@xxxxxxxxx>, "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Tue, 5 Jun 2007 15:57:58 -0400
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:
