This discussion is archived
12 Replies Latest reply: Nov 14, 2012 12:39 PM by KeithJamieson RSS

calculate the size of the clob datatype

782897 Newbie
Currently Being Moderated
i am trying to calculate the avg size of a row, in which i have one column which has clob datatype,

for rest of the datatypes i am using (avg(vsize(col_name,varchar2(10)))), i want a simple function that can calculate the clob datatype.
  • 1. Re: calculate the size of the clob datatype
    Stew Ashton Expert
    Currently Being Moderated
    How about avg(lengthb(clob_column))?
  • 2. Re: calculate the size of the clob datatype
    BluShadow Guru Moderator
    Currently Being Moderated
    oracedba wrote:
    i am trying to calculate the avg size of a row, in which i have one column which has clob datatype,
    For what purpose are you trying to calculate that?
    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.
  • 3. Re: calculate the size of the clob datatype
    782897 Newbie
    Currently Being Moderated
    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.
  • 4. Re: calculate the size of the clob datatype
    Martin Preiss Expert
    Currently Being Moderated
    perhaps: dbms_lob.getlength

    dbms_stats has some problems with the length of CLOBs in some versions: http://jonathanlewis.wordpress.com/2012/08/27/fragmentation/

    Edited by: mpreiss on Nov 14, 2012 2:13 PM
  • 5. Re: calculate the size of the clob datatype
    myOra_help Journeyer
    Currently Being Moderated
    You can use DBMS_LOB.GETLENGTH function to get the length of LOB datatype.It has below mentioned syntax.

    DBMS_LOB.GETLENGTH (
    lob_loc IN BLOB)
    RETURN INTEGER;

    DBMS_LOB.GETLENGTH (
    lob_loc IN CLOB CHARACTER SET ANY_CS)
    RETURN INTEGER;

    DBMS_LOB.GETLENGTH (
    file_loc IN BFILE)
    RETURN INTEGER;

    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
  • 6. Re: calculate the size of the clob datatype
    stefan nebesnak Journeyer
    Currently Being Moderated
    VSIZE returns the number of bytes.
    DBMS_LOB.getLength (<clob-column>) returns number of characters.

    However, CLOB can be passed into VSIZE as argument through implicit data conversion..
  • 7. Re: calculate the size of the clob datatype
    BluShadow Guru Moderator
    Currently Being Moderated
    oracedba wrote:
    I am trying to calculate the avg row length for a table,
    Again, I'll ask... For what purpose?

    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.
  • 8. Re: calculate the size of the clob datatype
    782897 Newbie
    Currently Being Moderated
    yes this is for volumetrics for future disk space,so that is the reason i am asking.
  • 9. Re: calculate the size of the clob datatype
    BluShadow Guru Moderator
    Currently Being Moderated
    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.
  • 10. Re: calculate the size of the clob datatype
    Stew Ashton Expert
    Currently Being Moderated
    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.

    Test data:
    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;
    Using length (which will give me number of characters, not number of bytes):
    select round(sum(length(clob_col))/1024/1024, 3) "Length Meg"
    from t;
    
    Length Meg
    ----------
         3.054 
    Real segment size (including space left over for LOB undo):
    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 
  • 11. Re: calculate the size of the clob datatype
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    oracedba wrote:
    yes this is for volumetrics for future disk space,so that is the reason i am asking.
    I agree with Blu - it is a pointless and meaningless exercise.

    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.
  • 12. Re: calculate the size of the clob datatype
    KeithJamieson Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points