Home » RDBMS Server » Server Administration » Weight of data types (10.2.0.3 on SOLARIS 9)
Weight of data types [message #302579] Tue, 26 February 2008 04:06 Go to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
Good morning,

I'm trying to find information on how much every oracle data type weight in a database.

For example, when I put a date in a table how much does it weight.

I need to know this or a bit more generally, how are they stored in the DB.

I know that characters are 1 byte but I wonder if integers are binaries and the number 2 would be 2 bits.

I have no idea for dates and decimal numbers.

Does anyone know about those things.

A link to the subject would be great too.

regards and thanks in advance,

Nico

Report message to a moderator

Re: Weight of data types [message #302583 is a reply to message #302579] Tue, 26 February 2008 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBA_TAB_COLUMNS.DATA_LENGTH.

Regards
Michel

Report message to a moderator

Re: Weight of data types [message #302587 is a reply to message #302579] Tue, 26 February 2008 04:30 Go to previous messageGo to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
Hi.

this is the data_length (how long) of every columns for every table.

I'm not talking about column size here, it's really how much space in bits or bytes a value of a certain type takes on the hard disk.

and I need to know that for:
date
float
integer
varchar2

Lobs are another subject.

How are they encoded?

at school i've learned counting in binaries for integer

0
1
10
11
100
101
110
111
1000

Does that apply for integers in the DB? what about dates and floats?

this is what I have in mind.

Nico

Report message to a moderator

Re: Weight of data types [message #302598 is a reply to message #302587] Tue, 26 February 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't have this information for a general answer.
The best you can have is data_length.
Otherwise, for current data, you can use vsize function.

Number are coded in proprietary format, no relation with binary.
Float, integer and other are NUMBER data type.

Date are 7 bytes long, for other time type use data_length.
And so on.

Regards
Michel

Report message to a moderator

Re: Weight of data types [message #302615 is a reply to message #302579] Tue, 26 February 2008 06:17 Go to previous messageGo to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
I have found an awesome document explaining the generalization!

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i2093

I stick that here for posterity.

The information it seems is hard to get.

I hope to save others time like that. Spread the word!!!

Report message to a moderator

Re: Weight of data types [message #302619 is a reply to message #302615] Tue, 26 February 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This document exists in each and every Oracle version.
It does not generalize anything and don't answer the question of "what is the number of bytes used by 12456000?".
For numbers, it gives the "good old" approximative and arbitrary formula.

Regards
Michel

Report message to a moderator

Re: Weight of data types [message #302622 is a reply to message #302579] Tue, 26 February 2008 06:45 Go to previous messageGo to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
It gives a few answer and when it's not possible to give the value for a given number, it does give a formula and that's generalizaton.

I can't really use this kind of formula because I'm writing a document for a crowd of not so technical people but it does tell me that it's best having numbers in number type rather than char because the latter would weight heavier;

I need validation and sources and that will do Smile

Thanks anyway

Report message to a moderator

Re: Weight of data types [message #302629 is a reply to message #302622] Tue, 26 February 2008 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it does tell me that it's best having numbers in number type rather than char

Oh! If this was the root question, you should post it, I could answer you immediately.
Bytes are not the only point.
Query performances depend on optimizer understanding which relies on your datatype. Numbers are not strings, they are used differently, they have not the same range of values and behaviours.
Otherwise, why not store all data in strings? Why bother with other datatypes? (And this what some do naming this generic model Sad )

Regards
Michel

Report message to a moderator

Re: Weight of data types [message #302632 is a reply to message #302579] Tue, 26 February 2008 06:56 Go to previous messageGo to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
believe it or not, I got an application that my company bought (a year before, I just joined) and of course now it wreck havocs and it does mix a lot of different values in just one field called "value".

of course there's a field with an ID which links to another table called "LABEL". I thought only beginners student could do that; It seems not.

Problem is my management don't even understand why having 115 lines for a policy is wrong!!!

I have to justify everything I say and it drives me crazy!
I know why we use types, I just couldn't find something official to justify it.

Report message to a moderator

Re: Weight of data types [message #302642 is a reply to message #302632] Tue, 26 February 2008 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
believe it or not,

I believe you, I saw it!

Quote:
I know why we use types, I just couldn't find something official to justify it.

I don't there is anything "official" as everything is in the end bits.
It is just 101 good programming practice: http://computerprogramming.suite101.com/article.cfm/datatypes or http://en.wikipedia.org/wiki/Data_type.
In Oracle, optimizer knows what is a datatype and its behaviour changes with it.

Regards
Michel

Report message to a moderator

Re: Weight of data types [message #302648 is a reply to message #302632] Tue, 26 February 2008 08:06 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Have a read here

Report message to a moderator

Previous Topic: Concern about moving tables
Next Topic: dbca hangs at 85%
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 16 05:58:42 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.