I am trying to figure out how to calculate the size of BTREE tables and
indexes in Ingres 6.4 and also the growth of the tables.

Any help would be greatly appreciated.

Thanks
Palani

Hi Palani,

select table_name,table_owner,table_type,number_pages/512. as megs
from iitables
where storage_structure='BTREE'
and number_pages/512. >= 10.0
order by megs desc;

Will list all BTREE tables or indexes in the database of at least 10M
size and order the output in size order.

To measure the growth of the tables you can simply store the output from
regular runs of ths query and then just compare them over time.

Martin Bowes

--
Is it just me, or is there a dark undercurrent beneath the fluff of
'Sabrina the Teenage Witch':
Aunt Hilda - Not all Tsunamis are bad.

Hi Martin.

We have databases that vary in page sizes: 2Kb, 4KB, and 8KB. Does the SQL you provided cater for this. Also, do yo have the SQL that records database table sizes for ISAM, HASH, and HEAP?

Thanks.

Jason

-----Original Message-----
From: Martin Bowes [mailto:bowes (AT) bucket (DOT) its.unimelb.edu.au]
Sent: Thursday, 27 November 2003 7:54 AM
To: Ramaiyan:Palanivel
Cc: ingres newsgroup
Subject: Re: Table size and growth


Hi Palani,

select table_name,table_owner,table_type,number_pages/512. as megs
from iitables
where storage_structure='BTREE'
and number_pages/512. >= 10.0
order by megs desc;

Will list all BTREE tables or indexes in the database of at least 10M
size and order the output in size order.

To measure the growth of the tables you can simply store the output from
regular runs of ths query and then just compare them over time.

Martin Bowes

--
Is it just me, or is there a dark undercurrent beneath the fluff of
'Sabrina the Teenage Witch':
Aunt Hilda - Not all Tsunamis are bad.


************************************************** **********************
The information in this e-mail together with any attachments is
intended only for the person or entity to which it is addressed
and may contain confidential and/or privileged material.
Any form of review, disclosure, modification, distribution
and/or publication of this e-mail message is prohibited.
If you have received this message in error, you are asked to
inform the sender as quickly as possible and delete this message
and any copies of this message from your computer and/or your
computer system network.
************************************************** **********************

Hi,

try the following query:

select table_name, allocated_pages * table_pagesize/1024 as size_in_kb , storage_structure
from iitables
where system_use = 'U'
and table_type in ( 'T', 'I' )
;

that might give you a base for further experimenting.
Also have a look at the documentation of the SCI (standard catalog interface).
It is all written down in the DBA guide if I'm not mistaken.

HTH

Ronald

On 7 Dec 2003 16:07:18 -0600
Jason.Tepoorten (AT) nrm (DOT) qld.gov.au (Tepoorten Jason) wrote:


--
independIT Integrative Technologies GmbH
Sitz der Gesellschaft: Schrobenhausen
HRB Neuburg B 1.521
Geschäftsführer:
Dieter Stubler, Dipl. Inform. (FH)
Ronald Jeninga, Diplom Mathematiker

Hi Jason,

I've been away most of December and I suppose that by now you've solved
this yourself.

If not, then then the answers are:
1. The code works on 2k pages only. The division by 512 asumes this.

2. The extension to other table types is easialy achieved by not
restricting the search to BTREE tables.

So assuming you are running Ingres 2.5 or better then you could alter the
code to allow for differences as follows:

declare global temporary table session.table_sizes as
select table_name,table_owner,table_type,storage_structur e,
case when table_pagesize=2048 then number_pages/512.
when table_pagesize=4096 then number_pages/256.
when table_pagesize=8192 then number_pages/128.
when table_pagesize=16384 then number_pages/64.
when table_pagesize=32768 then number_pages/32.
when table_pagesize=65536 then number_pages/16.
end as megs
from iitables
on commit preserve rows with norecovery;

select * from session.table_sizes where megs>1.0;

Marty

--
Is it just me, or is there a dark undercurrent beneath the fluff of
'Sabrina the Teenage Witch':
Sabrina about to be thrown to the Lions...
Aunt Zelda - Don't let them smell your fear.
Sabrina - Oh? So bravery will mask the smell of my succulent flesh?