RE: Table extentsize query

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>;