Re: Table extentsize query
But that's a different question, with a different answer. The queries to
see the size of each extent and the number and other stats of the extents
are:
select dbsname, tabname, size
from systabnames st, sysextents se
where st. partnum = se.partnum;
select dbsname, tabname, count(*) as num_extents, min(size) as
smallest_extent, max(size) as largest_extent
from systabnames st, sysextents se
where st. partnum = se.partnum;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Dec 21, 2009 at 6:13 AM, Habichtsberg, Reinhard <
RHabichtsberg@arz-emmendingen.de> wrote:
> Sorry, for me the query isn't much meaningful, though. The problem is that
> you will see the first extent size only, the next extent size, which is set
> dynamically is - in some cases - much more important. Unfortunately you
> cannot see how much extents of which size exist. If you build aggregate
> sums
> the result may or may not be accurate.
>
> In short: You can see, how many extents exist but you cannot see how large
> they are in particular.
>
> Reinhard.
>
> -----Original Message-----
> From: informix-list-bounces@iiug.org
> [mailto:informix-list-bounces@iiug.org]On Behalf Of Art Kagel
> Sent: Thursday, December 17, 2009 10:39 PM
> To: thomasgsmith
> Cc: informix-list@iiug.org
> Subject: Re: Table extentsize query
>
>
> If you are getting multiple rows from this query it is because the table is
> fragmented into that many partitions.
>
> BTW, using HEX() in the filter is slow BTW so your should removed it and
> just compare the partnums directly. The key is to select only fields that
> will be identical between the fragments, GROUP the query and select the
> MIN(), MAX(), or AVG() of the extent sizes (since all extents have the same
> size) it doesn't matter:
>
> select tabname[1,24], MIN( fextsiz ), MAX( nextns )
> from sysptnhdr syspt, systabnames systab
> where
> dbsname = "<target_database>" and
> tabname in
> (.
> .
> .)
> and
> hex(syspt.partnum) = hex(systab.partnum)
> group by 1
> order by 1;
>
> Art
>
> Art S. Kagel
> Oninit ( www.oninit.com <http://www.oninit.com>; )
> IIUG Board of Directors ( art@iiug.org <mailto:art@iiug.org> )
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf <http://www.iiug.org/conf>;
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
>
>
>
> On Thu, Dec 17, 2009 at 4:12 PM, thomasgsmith < thomasgsmith@verizon.net
> <mailto:thomasgsmith@verizon.net> > wrote:
>
>
> Does anybody have a query that returns tablename and extentsizes? I
> haven't quite gotten one to work vs. sysmaster -- it will return 2
> rows per table:
>
> select tabname[1,24], hex(syspt.partnum), fextsiz, nextns
> from sysptnhdr syspt, systabnames systab
> where
> dbsname = "<target_database>" and
> tabname in
> (.
> .
> .)
> and
> hex(syspt.partnum) = hex(systab.partnum)
> order by 1;
>
> My functional knowledge of sysmaster is obviously deficient --
> predicate correction would be appreciated. Want to avoid parsing slow
> oncheck output.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org <mailto:Informix-list@iiug.org>
> http://www.iiug.org/mailman/listinfo/informix-list
> <http://www.iiug.org/mailman/listinfo/informix-list>;
>
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>