| 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
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).Received on Wed May 22 2002 - 15:45:36 CDT
 |
 |