?
Solved

Column Calculation

Posted on 2009-05-03
4
Medium Priority
?
830 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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