This content has been marked as final. Show 12 replies
oracedba wrote:For what purpose are you trying to calculate that?
i am trying to calculate the avg size of a row, in which i have one column which has clob datatype,
CLOB's can be stored inline in the table, or in their own space outside the table, so "average size of a row" is a bit ambiguous and you need to be clearer about what you are trying to achieve.
I am trying to calculate the avg row length for a table,until now i dont have any problem until now as all my tables has varchar2() and date data types,
for this i was using select avg(vsize(nvl(col1,0)+avg(vsize(date)+so on all columns from table;
but when the datatype clob came in this did not work,so please provide me a solution to calculate the avg row size for all datatypes.i cant use compute stats as the business req. is not possible.
You can use DBMS_LOB.GETLENGTH function to get the length of LOB datatype.It has below mentioned syntax.
lob_loc IN BLOB)
lob_loc IN CLOB CHARACTER SET ANY_CS)
file_loc IN BFILE)
where file_loc The file locator for the LOB whose length is to be returned.
Hope it helps.
Remember while calculating the size of row, LOB is not actually stored in your table, it is stored outside the table. Only LOB_LOCATOR in stored in table structure.(It is just like a pointer in some other language like C)
Edited by: myOra_help on Nov 14, 2012 6:48 PM
oracedba wrote:Again, I'll ask... For what purpose?
I am trying to calculate the avg row length for a table,
If it's just to know the average row size on the base table, then you could be looking at just having the size of the lob locator on the table if the LOB is stored external to the table. If however, the LOB is stored inline to the table, then that would effect the row size on that table (which you could just use LENGTH or LENGTHB function to find out).
However, until you tell us what the real purpose of knowing the average row length is, it's difficult to tell you the correct answer.
Perhaps it's one of those stupid management requirements to try and determine how much disk space needs to be bought on a new server based on existing data usage and estimated growth.... pointless as disk space is cheap nowadays.
Just buy as much disk space as you can afford. Problem solved.
There are always far more important issues to be dealt with as part of the business and systems analysis than how much disk space is going to be used. Back in the 1990's when disk space was a premium... yes we would worry about those things, but nowadays, you can buy terrabytes of disk space for less than your own monthly salary.
In my previous post, I suggested lengthb. That will not work with a multi-byte character set.
the length() function and the dbms_lob.getlength() function will return exactly the same result: the length in characters, not bytes.
To get the number of bytes, you would have to convert the CLOB to a temporary BLOB, then get the length of the BLOB.
Anyway, that will not give you the size of the storage you need. To do that, look in USER_SEGMENTS.
Using length (which will give me number of characters, not number of bytes):
drop table t; create table t(clob_col) as select to_clob(rpad('é',2000,'é'))||rpad('a',4000,'a')||rpad('a',level*10,'a') from dual connect by level <= 400;
Real segment size (including space left over for LOB undo):
select round(sum(length(clob_col))/1024/1024, 3) "Length Meg" from t; Length Meg ---------- 3.054
variable table_name varchar2(30); exec :table_name := 'T'; select sum(bytes)/1024/1024 "Real Meg" from user_segments where segment_name in ( select val from ( select TABLE_NAME, SEGMENT_NAME, INDEX_NAME from user_lobs where TABLE_NAME = :table_name ) unpivot(val for col in(TABLE_NAME, SEGMENT_NAME, INDEX_NAME)) ); Real Meg ---------- 8.125
oracedba wrote:I agree with Blu - it is a pointless and meaningless exercise.
yes this is for volumetrics for future disk space,so that is the reason i am asking.
Space estimation for the modern database is fairly simply. Round it up to the nearest half TB for small databases and the nearest TB for medium to large databases.
As Blu indicated, LOBs can be stored in-line with a row, or a "pointer" in a row that points to a LOB segment chain.
Data blocks are not entirely used - as by default a certain percentage of a block is reserved for row changes in that block. Likewise a block that was full and now contains more than 50% free space can still be considered not for use - as used space of that block has not yet dropped to the low water market.
So counting average row sizes means what? You can have an accurate estimate of the physical space footprint of those rows?
What about processing those rows? Temp space, undo and redo, and archive logs? The frequency of changes to the table is equally relevant to the row count in that table.
What about unexpected growth - like materialising a view to increase performance at the expense of using additional space?
I personally find this whole "space estimate" thing a waste of time.
What you should ask is what the average size of a typical blob to be loaded will be. Then figure out how many rows will be in the table. Always overestimate the size rather than underestimate. so, if they say its 8K then use 10K.
so 10000 rows * 10K = 1, 000, 000 K.
Edited by: Keith Jamieson on Nov 14, 2012 8:39 PM grammar