Re: Usage of Number type for table columns

> In one of our applications, my boss wants to define all the> numeric columns as NUMBER or leave it as floating point. He> wants to define all the tables like this wherever numeric> column is defined in the table.> > Example:> > Location_ID NUMBER> > We know, from our application character, that this location_ID> would never cross beyond 3 digits.

Today you know this, but it could change, so allow for it.

> Also, we know that this specific column does not require any> digits to the right of the decimal point. ---> (This column is> only an example).

It sounds like you are going to have intelligence in this key. I
would recommend against that one.

> He does not want to specify any precision or scale for the> number type columns. His point is --- Oracle would use only> that much space depending on the actual number of digits he> enters into the column. Thus, he is not wasting any space.

But Oracle will have a bit of a performance hit when it joins to
this table on a number (I assume alot of these columns will be
PK's). I believe it is the same penalty that Oracle encounters
when it has to query a varchar vs char field, it has to figure
out how far to look with the varchar where it doesn't with a
char, and the opposite argument also holds, the varchar is much
more flexible than the char. So, if these are going to be used
to join tables together alot, you might want to consider a
straight integer sequence.

> Also, he says, during the beginning of application, we might> not know the maximum limits for number column. Hence, leaving> them as floating point ( Location_ID NUMBER), gives him> flexibility and he need not change the number column precision> or scale during the entire life cycle of the application.

Once again, it sounds like you have intelligence in these keys.

> He says, it is upto the application program (JAVA/EJB) to> control and check the maximum length permissible against a> column depending on the business rules.

Yes and no. It is ultimately up to the DB to protect itself, but
the app shouldn't rely on the DB's protection to get it correct.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Galen Boyer
INET: galenboyer_at_hotpop.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).