Solved

Column Calculation

Posted on 2009-05-03
4
828 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 250 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 250 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 shows how to recover a database from a user managed backup

696 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