Checking the number of multiple table records with a single query

There is a query that uses Oracle's getxmltype function to get the number of records in each table.

I have tested it for Informix.

First of all, here's an example of using the getxmltype function in Oracle

SELECT table_name

     , num_rows -- information about the number of table records in the catalog table

     , TO_NUMBER(

       dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM ' || table_name).Extract('//text()')

       ) num_rows2 -- Number of records by count function

  FROM user_tables

;

Executing the above query from the SCOTT account, which is sample data, shows the following results.

TABLE_NAME                       NUM_ROWS  NUM_ROWS2

------------------------------ ---------- ----------

DEPT                                    4          4

EMP                                    14         14

BONUS                                   0          0

SALGRADE                                5          5

TB_DF_STAT                             24         24

To use XML functions in Informix, the idsxmlvp class Virtual Processor must be running.

This can be set dynamically with the onconfig VPCLASS setting or the onmode -wm / -wf command.



In the Informix sample database stores_demo, we created the following query using the xml function:

The functions used are extractvalue, genxmlquery. These are built-in functions.

select tabname,

       nrows::int num_rows,

       extractvalue(genxmlquery('set','SELECT count(*) c FROM '||tabname),'/set/row/c')::int num_rows2 

from   systables where tabid > 99 and tabtype = 'T';

tabname     customer

num_rows    28

num_rows2   28

tabname     orders

num_rows    23

num_rows2   23

tabname     manufact

num_rows    9

num_rows2   9

tabname     stock

num_rows    74

num_rows2   74

tabname     items

num_rows    67

num_rows2   67

It's convenient because you can verify the number of cases with a single query. It seems to be good to use according to the purpose.

In addition to using the COUNT function, there seems to be a way to use it.

If the number of records is too large, the system may be overloaded due to high resource consumption such as memory and disk.


Reference:
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.xml.doc/ids_xpconfig.htm
http://www.gurubee.net/article/55486
http://www.gurubee.net/article/61164