Dear All, 

We normally use the following SQL to get the INFORMIX TABLE size.

select tabname , ((npused * pagesize) / 1073741824)) GB from systables where tabname matches  "<tab name> "

Does it mean " DATA and INDEX storage both " or do  we have to calculate  all the index sizes of the table separately and sum with above output ?

Thanks !

Best Regards,

Indika 

------------------------------
Indika Jinadasa
------------------------------

Indika:

First, except for VERY OLD tables that were created before v7.20 (where index pages were part of the table's partition - since 7.20 indexes have had their own separate partitions) the page counts for a table only include the table data itself and internal overhead, no index pages. Also systables does not have the details for partitioned tables, that would be in the sysfragments table entries for that table.

That said, first know that the page statistics in systables may be out-of-date. These are not maintained live but are only updated by UPDATE STATISTICS LOW or HIGH (not MEDIUM), and only HIGH if you do not include the DISTRIBUTIONS ONLY clause. It is better to use the sysmaster table sysptnhdr for the live details. Also, there are no page statistics data on indexes in the catalog tables for indexes so you have no choice but to go to sysmaster for that information anyway. You can do this:

select sum(npused) * pagesize
from systabnames st, sysptnhdr sp
where st.partnum = sp.partnum
 and st.tabname in ("mytablename", "myindex1", "myindex2", ... );

That will include both data and index pages. FYI, even if you only do the actual tablename and query the indexes separately, include the SUM(npused) because if the table is partitioned it will have multiple entries in both systabnames and sysptnhdr one for each partition/fragment. Same for partitioned indexes.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Wed November 08, 2023 05:38 AM
From: Indika Jinadasa
Subject: INFORMIX TABLE SIZE

Dear All, 

We normally use the following SQL to get the INFORMIX TABLE size.

select tabname , ((npused * pagesize) / 1073741824)) GB from systables where tabname matches  "<tab name> "

Does it mean " DATA and INDEX storage both " or do  we have to calculate  all the index sizes of the table separately and sum with above output ?

Thanks !

Best Regards,

Indika 

------------------------------
Indika Jinadasa
------------------------------