Size of table when BLOB colums exist

ggnanaraj · #1 (**permalink**) 12-30-05, 05:54

How do we calculate the table size when LOB/LONG columns exist in the table?

Without LOB/LONG, we can use TSIZE of 'reorgchk' output. However, this does not hold good if the table has LOB/LONG columns.

TIA.

dahalkar_p · #2 (**permalink**) 01-04-06, 02:02

Hi,

You need not consider LOB and LONG data size while sizing the tables. Table data pages do not contain the data for columns defined with LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, or DBCLOB data types. The rows in a table data page do, however, contain a descriptor for these columns which i guess takes 16 bytes of space. So while calculating row size add 16 bytes for such columns.

But remember to consider disk space for such columns as they are stored on disk anyway.

ggnanaraj · #3 (**permalink**) 01-04-06, 07:49

Quote:

Originally Posted by dahalkar_p

Hi,

You need not consider LOB and LONG data size while sizing the tables. Table data pages do not contain the data for columns defined with LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, or DBCLOB data types. The rows in a table data page do, however, contain a descriptor for these columns which i guess takes 16 bytes of space. So while calculating row size add 16 bytes for such columns.

But remember to consider disk space for such columns as they are stored on disk anyway.

Thanks for the input. I need to know the table size for 'BLOB column tables' also as I'm working on 'growth trend' - capacity planning - of the database over a period of few months.

When TSIZE is considered, the size of the database is 15 GB. However, the full backup image of the database is 200 GB.

TIA.