Is there a convenient way to find out the actual size -- the size in
(k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
just show me the name of the index and how big it is?

This is in IDS 7.31.FS6 if that matters.

Thanks.

--
Jus' livin' in the Dilbert Zone...

Number of rows * index size ?
If the index is in its own tablespace (will be if created using in
dbspace or by version 9 and 10) then oncheck -pt database:table wil
show you.

MW

(Thanks Version matters here)

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of Thomas Ronayne
Sent: Tuesday, 20 March 2007 8:36 a.m.
To: informix-list@iiug.org
Subject: Index Size

Is there a convenient way to find out the actual size -- the size in
(k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
just show me the name of the index and how big it is?

This is in IDS 7.31.FS6 if that matters.

Thanks.

--
Jus' livin' in the Dilbert Zone...
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Murray Wood wrote:
> Number of rows * index size ?
> If the index is in its own tablespace (will be if created using in
> dbspace or by version 9 and 10) then oncheck -pt database:table wil
>
Yeah, I kind of knew about that one but I was thinking of another way
(that I can't remember and can't find in the manuals) that shows the
actual size of indexes in pages or bytes or whatever for all the indexes
(especially the ones in a separate dbspaces).

But, this'll do, and thank you.
> show you.
>
> MW
>
> (Thanks Version matters here)
>
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org] On Behalf Of Thomas Ronayne
> Sent: Tuesday, 20 March 2007 8:36 a.m.
> To: informix-list@iiug.org
> Subject: Index Size
>
> Is there a convenient way to find out the actual size -- the size in
> (k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
> just show me the name of the index and how big it is?
>
> This is in IDS 7.31.FS6 if that matters.
>
> Thanks.
>
>

--
Jus' livin' in the Dilbert Zone...

On Mar 19, 4:36 pm, Thomas Ronayne <t...@REMOVETHISameritech.net>
wrote:
> Is there a convenient way to find out the actual size -- the size in
> (k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
> just show me the name of the index and how big it is?
>
> This is in IDS 7.31.FS6 if that matters.
>
> Thanks.
>
> --
> Jus' livin' in the Dilbert Zone...

Most convenient way: use ServerStudio (I don't recall which of the
packages has it, as I use the full suite). It will not only show you
the size of the index, but also a list of all the index' extents and
how much is used of the allocated size. Now, if they would also match
the extents to the partitions (and do a better job with partitions in
general) that would be great.

Thomas Ronayne wrote:
> Is there a convenient way to find out the actual size -- the size in
> (k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
> just show me the name of the index and how big it is?
>
> This is in IDS 7.31.FS6 if that matters.
>
> Thanks.
>
SELECT st.tabname, sp.npused as indexpages, (sp.npused * sd.pagesize / 1024)
as indexKB
FROM sysmaster:systabnames st,
sysmaster:systabnames st2,
sysmaster:sysptnhdr sp,
sysmaster:sysdbspaces sd
WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
AND st2.partnum = st.lockid
AND st.partnum = sp.partnum
AND sd.dbsnum = (sp.partnum /1048576)
;

This will show you space for all of the tables partitions including
fragments and detached indexes. To see ONLY index space add the following
filter:

AND st.tabname != st2.tabname

Art S. Kagel

On 20/03/07, Art S. Kagel <kagel@bloomberg.net> wrote:
> Thomas Ronayne wrote:
> > Is there a convenient way to find out the actual size -- the size in
> > (k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
> > just show me the name of the index and how big it is?
> >
> > This is in IDS 7.31.FS6 if that matters.
> >
> > Thanks.
> >
> SELECT st.tabname, sp.npused as indexpages, (sp.npused * sd.pagesize / 1024)
> as indexKB
> FROM sysmaster:systabnames st,
> sysmaster:systabnames st2,
> sysmaster:sysptnhdr sp,
> sysmaster:sysdbspaces sd
> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
> AND st2.partnum = st.lockid
> AND st.partnum = sp.partnum
> AND sd.dbsnum = (sp.partnum /1048576)
> ;
>
> This will show you space for all of the tables partitions including
> fragments and detached indexes. To see ONLY index space add the following
> filter:
>
> AND st.tabname != st2.tabname
>
> Art S. Kagel
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
Unfortunately this won't show the space taken up by attached indexes
(which are default on 7.31, the original poster's platform).

Keith

From the keyboard of "Keith Simmons" <smiley73@googlemail.com>:

> On 20/03/07, Art S. Kagel <kagel@bloomberg.net> wrote:
>> Thomas Ronayne wrote:
>> > Is there a convenient way to find out the actual size -- the
>> > size in (k|m|g)bytes of indexes? I mean without doing
>> > checking or rebuilding, just show me the name of the index
>> > and how big it is?
>> >
>> > This is in IDS 7.31.FS6 if that matters.
>> >
>> > Thanks.
>> >
>> SELECT st.tabname, sp.npused as indexpages, (sp.npused *
>> sd.pagesize / 1024) as indexKB
>> FROM sysmaster:systabnames st,
>> sysmaster:systabnames st2,
>> sysmaster:sysptnhdr sp,
>> sysmaster:sysdbspaces sd
>> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
>> AND st2.partnum = st.lockid
>> AND st.partnum = sp.partnum
>> AND sd.dbsnum = (sp.partnum /1048576)
>> ;
>>
>> This will show you space for all of the tables partitions
>> including fragments and detached indexes. To see ONLY index
>> space add the following filter:
>>
>> AND st.tabname != st2.tabname
>>
>> Art S. Kagel
>>
> Unfortunately this won't show the space taken up by attached
> indexes (which are default on 7.31, the original poster's
> platform).

That info you get from

select count(*), a.tp_type
from sysmaster:systabpagtypes a
where a.tp_partnum = <partnum> group by a.tp_type

Where

tp_type=0: Free pages
tp_type=4: Data pages with room for another row
tp_type=12: Data pages without room for another row
tp_type=8: Index and bitmap pages
tp_type=2: Empty BLOBs pages
tp_type=6: Semi-Full BLOBLs pages
tp_type=10: Full BLOBs pages
tp_type=14: Very-Full BLOBs pages

That is, tp_type=8 counts the pages of attached indices.

HTH and best regards
Christian
--
#include <std_disclaimer.h>
/* The opinions stated above are my own and not
necessarily those of my employer. */

Keith Simmons wrote:
> On 20/03/07, Art S. Kagel <kagel@bloomberg.net> wrote:
>
>> Thomas Ronayne wrote:
>> > Is there a convenient way to find out the actual size -- the size in
>> > (k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
>> > just show me the name of the index and how big it is?
>> >
>> > This is in IDS 7.31.FS6 if that matters.
>> >
>> > Thanks.
>> >
>> SELECT st.tabname, sp.npused as indexpages, (sp.npused * sd.pagesize /
>> 1024)
>> as indexKB
>> FROM sysmaster:systabnames st,
>> sysmaster:systabnames st2,
>> sysmaster:sysptnhdr sp,
>> sysmaster:sysdbspaces sd
>> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
>> AND st2.partnum = st.lockid
>> AND st.partnum = sp.partnum
>> AND sd.dbsnum = (sp.partnum /1048576)
>> ;
>>
>> This will show you space for all of the tables partitions including
>> fragments and detached indexes. To see ONLY index space add the
>> following
>> filter:
>>
>> AND st.tabname != st2.tabname
>>
>> Art S. Kagel
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>>
> Unfortunately this won't show the space taken up by attached indexes
> (which are default on 7.31, the original poster's platform).

Arg! Missed the version info in the original post. For attached indexes,
and to adjust the above for 7.31:

SELECT st.tabname, sp.npused as indexpages,
(sp.npused * 2) as indexKB
FROM sysmaster:systabnames st,
sysmaster:systabnames st2,
sysmaster:sysptnhdr sp,
sysmaster:sysdbspaces sd
WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
AND st2.partnum = st.lockid
AND st.partnum = sp.partnum
AND sd.dbsnum = (sp.partnum /1048576)
AND st.tabname != st2.tabname
UNION ALL
SELECT st.tabname, sp.npused as indexpages,
((sp.npused - npdata) * 2) as indexKB
FROM sysmaster:systabnames st,
sysmaster:systabnames st2,
sysmaster:sysptnhdr sp,
sysmaster:sysdbspaces sd
WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
AND st2.partnum = st.lockid
AND st.partnum = sp.partnum
AND sd.dbsnum = (sp.partnum /1048576)
AND st.tabname = st2.tabname
;

Art S. Kagel

Art S. Kagel wrote:
> <SNIP>

Typo in both queries. st.lockid should be sp.lockid.

Art S. Kagel

> Should mention that the '* 2' below in each query should be changed to
> '* 4' on Windows and AIX where the pagesize is 4K rather than 2K. This
> works for 9.xx also and the 10/11 example in my original post doesn't
> need the pagesize adjustment by platform, it's built in.
>
> Art S. Kagel
>
>>> Unfortunately this won't show the space taken up by attached indexes
>>> (which are default on 7.31, the original poster's platform).
>>
>>
>>
>> Arg! Missed the version info in the original post. For attached
>> indexes, and to adjust the above for 7.31:
>>
>> SELECT st.tabname, sp.npused as indexpages,
>> (sp.npused * 2) as indexKB
>> FROM sysmaster:systabnames st,
>> sysmaster:systabnames st2,
>> sysmaster:sysptnhdr sp,
>> sysmaster:sysdbspaces sd
>> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
>> AND st2.partnum = st.lockid
>> AND st.partnum = sp.partnum
>> AND sd.dbsnum = (sp.partnum /1048576)
>> AND st.tabname != st2.tabname
>> UNION ALL
>> SELECT st.tabname, sp.npused as indexpages,
>> ((sp.npused - npdata) * 2) as indexKB
>> FROM sysmaster:systabnames st,
>> sysmaster:systabnames st2,
>> sysmaster:sysptnhdr sp,
>> sysmaster:sysdbspaces sd
>> WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
>> AND st2.partnum = st.lockid
>> AND st.partnum = sp.partnum
>> AND sd.dbsnum = (sp.partnum /1048576)
>> AND st.tabname = st2.tabname
>> ;
>>
>> Art S. Kagel

Thomas Ronayne wrote:
> Is there a convenient way to find out the actual size -- the size in
> (k|m|g)bytes of indexes? I mean without doing checking or rebuilding,
> just show me the name of the index and how big it is?
>
> This is in IDS 7.31.FS6 if that matters.
>
> Thanks.
>
Thanks to all for the advice and code (Art, the 7.x version runs for 15
minutes before I killed it; I'll see if I can figure out why).

--
Jus' livin' in the Dilbert Zone...