| DB tuning [message #204524] |
Tue, 21 November 2006 03:05  |
ataufique
Messages: 79
Registered: November 2006
|
Member |
|
|
What will be better to use during designing of DB
Number(5) or Char(5) or Varchar2(5).
The column data are 00123,00165,05623,.....
Here 0(Zero) in the begining is reqd for my usage..
|
|
|
|
| Re: DB tuning [message #204532 is a reply to message #204524] |
Tue, 21 November 2006 03:14  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If the leading zeros are required, then you've got String data rather than numberic, so you should use VARCHAR2.
If the maximum length of the data is 5 chrs (and you're using a single byte character set, then you want to use VARCHAR2(5)
|
|
|
|
| Re: DB tuning [message #204577 is a reply to message #204532] |
Tue, 21 November 2006 05:37 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
IMHO using correct data types is preferrable:
1. Your db will perform numeric checks for NUMBER(5) columns and
prevent anybody accessing the data directly to mess it up by inserting 'ABCDE' atring.
2. You cal always format the data disply with LPAD ( <column_name>, 5, '0').
3. Numeric data needs a bit less disk space, so you table and indexes are going to be more compact, requiring less buffer_gets/IO to access it.
HTH.
|
|
|
|
| Re: DB tuning [message #204599 is a reply to message #204524] |
Tue, 21 November 2006 06:24 |
vshari
Messages: 9
Registered: October 2005
Location: India/UK
|
Junior Member |
|
|
Hi,
It all depends on the requirements and what the application intends to with the column.
If you wish to store alphanumeric values or keep the preceding zeros then you will need to use the char or varchar2.
If you intend to use the column where only numbers will be stored then use NUMBER.
The varchar2 stores the values as required but char data type is different. This may create comparision problems.
It depends for what column is being used for in the application.
Besides i read somewhere(not able get the source now) using numbers/char/varchar2 also influences the way the optimizer analysing queries based on the column/table. The number columns are treated differently to the varchar2 columns (especially during range searches).
Please correct me if i am wrong.
Thanks,
Srihari V.
|
|
|
|
| Re: DB tuning [message #204650 is a reply to message #204577] |
Tue, 21 November 2006 09:02 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
I'd say that if the leading zeros are significant, then you're not storing numeric data, you're storing strings.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
