| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Data Warehouse experts, a simple question for you
If the data does not contain characters, AND NEVER WILL, it should be stored as a number. This prevents alphabetic "O's" from being stored with the data. If you really have to prefix the numbers with zeroes then do it on output. If the field itself is character and left padded with zeroes then you are going to see a lot of queries along the lines of
where <padded_field> like '%<significant_portion_of _number%' this won't use and index even though it might identify a unique record. A function-based index will not help here.
Ian MacGregor
Stanford Linear Acclerator Center
ian_at_SLAC.STANFORD.edu
-----Original Message-----
Sent: Wednesday, May 22, 2002 1:46 PM
To: Multiple recipients of list ORACLE-L
your fields should be consistent across tables, otherwise, you risk losing the ability for your queries to use an index if necessary.
If state code is char in one and num in the other, consider conversion on one of them, otherwise, oracle may do an implicit conversion on one of them during your queries.
For instance, if you write a query that says
SELECT *
FROM tab_a, tab_b
where tab_a.state_code = tab_b.state_code;
and these columns are two different datatypes, Oracle will actually run code similiar to the following:
SELECT *
FROM tab_a, tab_b
where to_num(tab_a.state_code) = tab_b.state_code;
and the use of this function in the where clause will disable the availability of an index on that column. The reason is that the index will be in characters and the value you are seeking will be a number.
You can use function based indexes to work around, but probably just better to store it the same in the first place.
hth,
/jack
> Paula_Stankus
> @doh.state.fl To:
> Multiple recipients of list
> ORACLE-L
> .us
> <ORACLE-L_at_fatcity.com>
> Sent by: root cc:
> Subject:
> RE: Data Warehouse
> experts, a simple
> question
> for you
> 05/21/2002
> 08:28 PM
> Please
> respond to
> ORACLE-L
>
>
>
>
>
>
>
>
> Okay you guys are silly. I have probably a stupid
> basic question to ask.
> How important is it to store data (let's say state
> codes, county codes with
> leading zeroes as character versus numeric). What is
> the standard out
> there? Does '02' mean the same thing as 2 for state
> code if you are
> consistent throughout your warehouse or do we need
> to consider other
> datasets out there that might be linked maybe
> sometime in the future? Can
> I leave it as is numeric and create materialized
> views with it padded or
> should I bite the bullet and reload into
> char/varchar2 datatypes?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed May 22 2002 - 16:20:57 CDT
 |
 |