Solved

Column Calculation

Posted on 2009-05-03
4
820 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
  • 2
4 Comments
 
LVL 73

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 73

Expert Comment

by:sdstuber
ID: 24292402
glad we could help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now