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 [EMAIL PROTECTED] -----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 > <[EMAIL PROTECTED]> > 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? __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
