Search code examples
sqloracle-databasecountgroup-byclob

Oracle, CLOB sizes report

I have a table with a Clob column. I would like to create a report that will tell me how many rows exist for each group of X CLOB size

For example if the step is 100K:

 Row Count   Clob size
----------- -------------
 10            0k - 100K
 5           100k - 200K
 3           200k - 300K

How to query and dynamically set the step size by 100 characters or 100K character?

Solution

  • Something like this ought to work (nb. I've used blocks of 10kb; you'd need to amend to whatever size blocks that you were after):

    select row_count,
       clob_size_group||' - '||(clob_size_group + 10) clob_size_group
from   (select count(*) row_count,
               floor((dbms_lob.getlength(clob_col)/1000) -- kb
                  /10)*10 clob_size_group -- separate into groups of 10kb
        from   your_table
        group by floor((dbms_lob.getlength(clob_col)/1000)
                    /10)*10)
order by clob_size_group;

    Also, it's worth noting that dbms_lob.getlength will return the number of characters for a CLOB rather than the number of bytes (which it would for a BLOB), so that may be different from the actual size if you have multibyte characters.