2

I can get a crude estimate of table sizes with

SELECT tabname, (rowsize*nrows)/1024/1024/1024 AS tabsize 
FROM informix.systables 
ORDER BY tabsize DESC

Is there a way to get a more accurate measurement of the size of the tables?

1 Answer 1

4

There is a lot of nuances to this question. Like do you want the size of the total number of pages currently allocated for use (so this would include pages available to be used by the table...so pages reserved for use by only this table, but that currently don't have any data on them), or do you only want the size of the currently used pages? Do you want to include the size of any indexes that would be defined on the the table?

So here is a query that could be used that would get the size of any non-system catalog table in the current database connected to that is the size of all the space reserved for the table, not including detached indexes:

    select a.tabname, (b.nptotal * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

Sample output from my tiny test system (note that t3 is a fragmented table where 1 fragment has no data, so you could use sum and group by if you wanted to report at a table level rather then fragment level if the your schema included fragmented tables)

    tabname  t1
    tabsize  1.5258789063e-05

    tabname  t2
    tabsize  1.5258789063e-05

    tabname  t3
    tabsize  0.00000000000000

    tabname  t3
    tabsize  1.5258789063e-05

If you change the query to the following, it would change the results to only count the pages that are currently used for the table, not all the space that is reserved for use:

    select a.tabname, (b.npused * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

If you want the sizes of detached indexes to be included in the table sizes, you would then have to include sysfragments into the query as well (would probably be a union of 1 form of the above 2 queries along with something that then got the index partition information by also then including the sysfragments into the join as well)

2
  • Thank very much. I will try the query you provided. In case it reduces ambiguity, the exact thing I’m tryingn to determine is how large each table would be if it was extracted into a csv
    – stevec
    Commented Aug 21, 2019 at 14:51
  • Ok, in that case, you would want to use the 2nd version of the query over the 1st for sure. However, it's also going to be inaccurate as well depending on how full each of the data pages ends up being. If your schema uses varchars then your original query is going to be the maximum size the table could be (since that's what rowsize is computed as). The query I provided will also be inaccurate depending on how much wasted space per page you have. Also, it just occurred to me that if you have data types that are not stored in row (like clob/blob for example) that will also affect the results
    – jrenaut
    Commented Aug 21, 2019 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.