Column Calculation

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.



 
VanJavaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
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
 
mrjoltcolaConnect With a Mentor Commented:
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
 
VanJavaAuthor Commented:
Thank you guys now I can clear my head =)
0
 
sdstuberCommented:
glad we could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.