Index Size
Poster asked how to see actual index sizes (in KB/MB) in IDS 7.31 without running oncheck-style rebuilds. Suggestions: oncheck -pt database:table (works when the index is in its own tablespace), estimating rows * index size, or using ServerStudio, which also shows extents and used space. Art Kagel supplied sysmaster queries joining systabnames, sysptnhdr and sysdbspaces to report index pages/KB; a follow-up noted that version only covers detached indexes, so he posted a UNION ALL variant handling 7.31's attached indexes (subtracting npdata). The poster thanked everyone but reported the 7.x query ran 15 minutes before he killed it, so no fully confirmed result.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
\
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
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...