July 2008

Ingres: Tables and data files

I recently needed to know how much disk space each table in a Ingres 2006 R2 database took. Any Ingres DBA knows that by default Ingres stores the data under location II_DATABASE, but the filenames used for the data files are not too informative. Fortunately it is possible to check the filename of a table using the following query in the corresponding database:

SELECT file_name, file_ext FROM iifile_info WHERE table_name = 'mytable';

The file_name column contains the name of the file for the table without extension and the column file_ext contains the extension. If the database uses multiple locations, it is also necessary to check the value of the column ‘location’, which will tell you the location of the file.

But this is not all. Ingres also stores all secondary indexes in separate files. It is also possible to see, using the table iifile_info, which index is stored in which file. This way it is even possible to hunt down indices that require more disk space than you expected.