An Ingres Blog

The following is SQL code was published in the #ingres IRC channel on freenode in response to a question about the best method to determine which iietab_xx_yy table belongs to which table:

select r1.relid as base_table,
       c.attname as column_name,
       r2.relid as extend_table
from iirelation r1,iirelation r2,iiattribute c,iiextended_relation e
where r1.reltid=e.etab_base and r2.reltid=e.etab_extension
  and r1.reltid=c.attrelid
  and c.attid=e.etab_attid
  and r1.reltidx=0
  and c.attrelidx=0
order by base_table,column_name

Running this against my demodb database I get the following:

┌────────────────────────────────┬────────────────────────────────┬────────────────────────────────┐
│base_table                      │column_name                     │extend_table                    │
├────────────────────────────────┼────────────────────────────────┼────────────────────────────────┤
│nclob                           │ntext                           │iietab_367_368                  │
│nclob2                          │ntext                           │iietab_369_36a                  │
│nclob2                          │ntext2                          │iietab_369_36b                  │
│nclob3                          │ntext                           │iietab_3c0_3c1                  │
│user_profile                    │up_image                        │iietab_f5_f6                    │
└────────────────────────────────┴────────────────────────────────┴────────────────────────────────┘
(5 rows)

Extending the above query we can also determine the size of each iietab_xx_yy table:

select r1.relid as base_table,
       c.attname as column_name,
       r2.relid as extend_table,
	   t.allocated_pages * float4(t.table_pagesize) / 1024 / 1024
       as megabytes
from iirelation r1, iirelation r2,
     iiattribute c, iiextended_relation e,
     iitables t
where r1.reltid=e.etab_base
  and r2.reltid=e.etab_extension
  and r1.reltid=c.attrelid
  and c.attid=e.etab_attid
  and r1.reltidx=0
  and c.attrelidx=0
  and t.table_reltid = e.etab_extension
  order by megabytes desc;

For example:

┌────────────────────────────────┬────────────────────────────────┬────────────────────────────────┬───────────┐
│base_table                      │column_name                     │extend_table                    │megabytes  │
├────────────────────────────────┼────────────────────────────────┼────────────────────────────────┼───────────┤
│nclob2                          │ntext2                          │iietab_369_36b                  │      1.258│
│nclob2                          │ntext                           │iietab_369_36a                  │      0.258│
│nclob                           │ntext                           │iietab_367_368                  │      0.258│
│nclob3                          │ntext                           │iietab_3c0_3c1                  │      0.258│
│user_profile                    │up_image                        │iietab_f5_f6                    │      0.258│
└────────────────────────────────┴────────────────────────────────┴────────────────────────────────┴───────────┘

Useful if you want to know how much space your BLOB/CLOB data is taking up. Thanks to Kristoff, Paul and Gerhard for working out the SQL.

Updated to recognize Kristoff Picards’ contribution