Home » RDBMS Server » Server Administration » data storage (Oracle 10g, 10.2.0.4.0, Solaris 10)
| data storage [message #458802] |
Tue, 01 June 2010 22:55  |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
I do want to estimate the size of a new database (re: DB2010E + additional new tables) that will be installed in our existing server. This new db will be an enhancement of the old db (re: DB2010) installed before in the said server. As my initial step, I will based such estimation on the existing table structure.
How many bytes does the data type declared as NUMBER will be allocated? And how about the data type declared as NUMBER(5)?
Or NUMBER(10,2)?
Correct me if I'm wrong, for I knew that VARCHAR2 data type will store no. of bytes based on the length it was declared, e.g. VARCHAR2(50)-- will store 50 characters/bytes, while DATE data type will store a default of 7 bytes.
Would someone enlighten me on this? Thanks in advance.
|
|
|
|
|
|
| Re: data storage [message #458808 is a reply to message #458803] |
Tue, 01 June 2010 23:25  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:How many bytes does the data type declared as NUMBER will be allocated?
It depends on the data.
Quote:Correct me if I'm wrong, for I knew that VARCHAR2 data type will store no. of bytes based on the length it was declared, e.g. VARCHAR2(50)-- will store 50 characters/bytes,
You are wrong. Number of bytes is the one to store the characters of your data (plus some overhead).
Quote:I will based such estimation on the existing table structure.
So it is easy to get the average of bytes from your current data and report it for your new tables.
Regards
Michel
[Updated on: Tue, 01 June 2010 23:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: data storage [message #459036 is a reply to message #458820] |
Thu, 03 June 2010 02:22 |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
Sir, it's a new thing to me, re: DBMS_SPACE.CREATE_TABLE_COST procedures.
But as a starter, can I use the SELECT DUMP syntax?
I created a test table and use the SELECT DUMP(COLUMN) FROM TABLE_NAME to know the length (in bytes) of certain data types.
In this example table(REYMTEST), I declare 'NUMBER' data types with diff. parameters:
NUMBER(16,2)
NUMBER
NUMBER(10)
NUMBER(5)
NUMBER(10,2)
NUMBER(2)
SQL> create table REYMTEST(
EX number(16,2), GAMMA number, SUN number(10),
ATOM number(5), RAYS varchar(10), HYDRO number(10,2),
MEGA number(2));
SQL> insert into REYMTEST values(12345678901234.12, 12345678901234567890, 1111111190, 55555, 'difrntrays', 1234567890.12, 99);
I extracted the supposed length(Len=) using the DUMP command:
DUMP(EX)
-----------------------------------------------
Typ=2 Len=9: 199,13,35,57,79,91,13,35,100
DUMP(GAMMA)
-----------------------------------------------
Typ=2 Len=11: 202,13,35,57,79,91,13,35,57,79,91
DUMP(SUN)
-----------------------------------------------
Typ=2 Len=6: 197,12,12,12,12,91
DUMP(ATOM)
-----------------------------------------------
Typ=2 Len=4: 195,6,56,56
DUMP(HYDRO)
-----------------------------------------------
Typ=2 Len=7: 197,13,35,57,79,91,13
DUMP(MEGA)
-----------------------------------------------
Typ=2 Len=2: 193,100
Is this fair enough to come up with a rough estimate of a table structure size (without data)?
Thanks again.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 05:51:14 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
