Size of a Table in Oracle

[Former Member]

Hi,

What is the exact way to find the size of the table. Following two methods shows two different values, wondering what is the exact way and why is this conflict

1. Function Module DB_GET_TABLE_SIZE - Looks like this multiplies the number of records with structure length and divided by 1024 to show the result in KB

2. DB02OLD - Current Sizes - Select Table

DB02 - Space-> Segments-> Table

The above two gives different results. Thanks in advance for guiding me.

Regards

Vijay

[Former Member]

Hi Vijay,

The two values will always be different: one gives you the physical size of the table in the Oracle DB, the other is a (rough) indicator of the size of the actual data.

The value in DB02(OLD) is the total amount of physical space (i.e. the size of the segment) the table occupies in the database. Although there is usually a relationship between the amount of data in the table and its segment size, this is not always true. For example, if a table once contained millions of rows but most of these have been deleted, then the segment is likely to be much larger than the amount of data still in the table; this is because by default Oracle will not shrink an oversized segment.

Measuring the space taken by actual table rows is more difficult. A reasonable approach is to take the number of rows and multiply it by the average row length. Both values are part of the Oracle database statistics. Again, a few warnings are in place however:

1) For large tables statistics are estimated rather than computed exactly, which means the value are not necessarily accurate

2) The SAP BR tools are optimized to only recalculate statistics for a table when its row count has changed by a sufficient amount (default 50%). For the Oracle optimizer, which is the prime consumer of statistical data, this is good enough, but for space estimates this would result in very inaccurate figures.

3) For tables containing RAW / BLOB fields (including SAP pools, SAP clusters and some other tables), the row length in the statistics does not include these fields, again leading to wrong results.

Function DB_GET_TABLE_SIZE uses the (row count * average length) formula. Looking at the source code it does not take the row length from the database but uses the structure length from the dictionary. I think this will overestimate the actual space in most cases, but it is probably better than the shake average row length from the DB stats.

Hope this helps,

Mark

[Former Member]

Thanks Marc for your detailed clear explanation. We are behind the space statistics for a particular infocube in BW i.e space growth of a particular infocube, space savings if we remove that cube, etc.. For this type of considerations which method do you think if we consider is better?

Regards

VIjay