RE: Storing single numbers in the database

From: Boyle, Christopher <Christopher.Boyle_at_MultiPlan.com>
Date: Tue, 5 Jun 2007 15:57:58 -0400
Message-ID: <D2DE8C70CA26DA429514E2332200F441629520@mail_02.multiplan.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_multiplan.com, 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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 05 2007 - 14:57:58 CDT