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
Popularity: 41% [?]
