| average row size [message #54038] |
Thu, 24 October 2002 23:59  |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
I want to find out the average row size of my emp table.
My select * from emp looks like this
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
3319 vinayaka Manager 1000 01-DEC-00 10000 500
10
3318 Krishna Manager 1000 01-DEC-00 1000 500
10
3379 rajesh Manager 1000 01-DEC-00 2000 500
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
3380 daya Manager 1000 01-DEC-00 10000 500
10
I used this query.
SELECT AVG (
NVL (VSIZE (EMPNO ),0)
+ 1
+ NVL (VSIZE (ENAME),0)
+ 1
+ NVL (VSIZE (JOB ),0)
+ 1
+ NVL (VSIZE (MGR ),0)
+ 1
+ NVL (VSIZE (HIREDATE ),0)
+ 1
+ NVL (VSIZE ( SAL ),0)
+ 1
+ NVL (VSIZE (COMM),0)
+ 1
+ NVL (VSIZE (DEPTNO),0))
from scott.emp;
The answer i got is 38.25
Is it correct?
Also is the answer in bytes?
Please reply to my email id
Thanks
|
|
|
|
| Re: average row size [message #54047 is a reply to message #54038] |
Fri, 25 October 2002 12:50  |
billh
Messages: 35
Registered: August 1999
|
Member |
|
|
2 methods
select avg(nvl(vsize(Col1),0))+
avg(nvl(vsize(col2),0))+
avg(nvl(vsize(col3),0))+
avg(nvl(vsize(col4 ),0))avg_row_length
from tab1;
or
analyze table tab1 compute/estimate statistics ... ;
select table_name, avg_row_len
from all_tables
where owner = '?????' ;
|
|
|
|
| Re: average row size [message #54078 is a reply to message #54038] |
Mon, 28 October 2002 15:56 |
Tommy Petersen
Messages: 5
Registered: October 2002
|
Junior Member |
|
|
That is a fairly cumbersome, and inaccurate way of getting the average row size.
Just run an ANALYZE on the table and query user_tables, it has a column for average row size.
|
|
|
|
| Re: average row size [message #54595 is a reply to message #54038] |
Fri, 22 November 2002 12:31 |
Trifon Anguelov
Messages: 514
Registered: June 2002
|
Senior Member |
|
|
The formula for calculating the row size is:
row_size = round((( length((p) + s) / 2)) + 1
where:
p - for varchar2 expected number of characters for column, for number where p = number of ditits and s = 0 for positive and 1 for negative, for date use 7
+ 1 byte per column in row
+ 3 byte row overhead per row
Hope that helps,
clio_usa
OCP - DBA
Visit our Web site
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
