[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Column Calculation

Posted on 2009-05-03
4
Medium Priority
?
834 Views
Last Modified: 2013-12-07
Hi, I have this table:
Column Name      Data Type        
BookNo                           Number(10)        
CustNo                           Varchar2(6)
RoomNo                           Number(3)
InvoiceNo                           Varchar2(10)
BookDate                           Date
NoG                           Number(3)
CiD                           Date
CoD                           Date
LoS                           Number(3)
CCNo                           Char(12)
CCType                           Varchar2(6)
CCExpiryDate      Date
Status                           Varchar2(10)

e.g. Number(3)   = (3/2)+1 = 3
       Varchar(10) = 10+1   =11
       Char(2)         = 2+1     =3
       Date              = 7+1     = 8
       Total                            =24

        #Total column size = 24+1=25 (this is just an example)
 
A. Does my calculation method for each different data type correct? Why we have to add 1 and why for Date datatype we calculate as 7?

B. And say if the total column size above = 200 and the row header size is 3, then how to calculate the row size? Is it 3+200+13(total column in booking table)?

Thank you b4 for anyone who helps me.



 
0
Comment
Question by:VanJava
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 24289782
you shouldn't add 1 to any of them.

number and varchar2 types grow as needed to store their data.  with a minimum bytes of 2 for numbers.
dates take up 7 bytes because that's what the internal storage requires.

char types always have the same size, that is what ever the value is.  so,  char(3) consumes 3 bytes.


of course, the char and varchar2 sizes assume single byte characters  If you are using multi-byte characters (unicode) you will have to double them.


so, the size of a row of data will be dependent on the values of the data in it if it has varchar2 or number columns.


use vsize() function to determine how many bytes a particular value is.   So, to calculate the size of row, you just add up all the vsize's


select vsize(bookno) + vsize(custno) + vsize(roomno) + vsize(invoiceno) + vsize(bookdate) + vsize(nog) + ... from your_table





0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 1000 total points
ID: 24290172
Sean is correct, but I am curious on the meaning behind your question, and adding 1 to each field.

Are you are trying to calculate row size for space estimates? I assume you are adding 1 to each column because of an understanding you have about Oracle's internal storage overhead? It is true that a VARCHAR has a small overhead, but that overhead is usually made up for, compared to CHAR, because VARCHAR's will not be space padded. It is considered, nowadays, good practice to use VARCHAR in almost all cases as most application programmers do not want space characters magically added to their content.

But to the rest of your calculations, adding the 1 overhead may or may not be approximately accurate. For example, Oracle, DB2 and Informix all store VARCHAR, NUMBER and DATE differently, or at least it is undefined behaviour, so it could change between versions.

The proper way to accurately calculate rowsize/storage in Oracle is to create your table, insert some test data, and then run analyze. You can try different values for pctfree on the table, insert a good size of sample data, and then analyze table ... compute statistics;

Then query DBA_TABLES or USER_TABLES to see the ROW statistics.

I added the CHAIN_CNT in the query below, in case you want to play around with the sample data and perform some updates, but since this test just inserts data, but does not update, there will be no CHAIN_CNT to start with, Oracle will choose a new block rather than chain the row on insert.

-- Run this whole script with different values of PCTFREE, you will see the storage differences
 
drop table T;
 
create table T (
BookNo                           Number(10),
CustNo                           Varchar2(6),
RoomNo                           Number(3),
InvoiceNo                           Varchar2(10),
BookDate                           Date,
NoG                           Number(3),
CiD                           Date,
CoD                           Date,
LoS                           Number(3),
CCNo                           Char(12),
CCType                           Varchar2(6),
CCExpiryDate      Date,
Status                           Varchar2(10)
)
pctfree 20;
 
-- Use max sizes for all data fields
begin
for i in 1..10000 loop
   insert into t values(9999999999, '123456', 999, '9999999999', sysdate, 999, sysdate, sysdate, 999, '123456789012', '123456', sysdate, '1234567890');
end loop;
end;
/
 
commit;
 
analyze table t compute statistics;
 
select num_rows, avg_row_len, avg_space, blocks, chain_cnt from user_Tables where table_name = 'T';

Open in new window

0
 

Author Closing Comment

by:VanJava
ID: 31577297
Thank you guys now I can clear my head =)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24292402
glad we could help
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question