Home » RDBMS Server » Performance Tuning » DB tuning
DB tuning [message #204524] Tue, 21 November 2006 03:05 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: GATHER_SCHEMA_STATS - monitoring progress?
Next Topic: Which Partition to Apply? What should be the partition key?
Goto Forum:
  


Current Time: Sat Dec 10 18:24:48 CST 2016

Total time taken to generate the page: 0.20346 seconds