how to determine say top 10 tables in size?
The poster asked, on IDS 9.4, for a quick SQL way to list the ten largest tables by space used instead of checking them one by one. Replies suggested sysmaster's systabinfo joined to systabnames, and an approximation via systables (rowsize*nrows) after update statistics. The answer the poster accepted was Mark Jamison's: "select first 10 tabname, npused from systables where tabid>99 and tabtype='T' order by npused desc" (run update statistics low first), or a server-wide version joining sysmaster's systabnames and sysptnhdr on partnum, which also shows catalog tables.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
With 9.4 is there a quick and easy way to see the top 10 (or so) biggest
tables in terms of used space? I know of various manual ways to do
this...including simply using dbaccess to display one by one..or perhaps
looking at output of dbexport or even the actual *.dat files. But is there a
sysmaster view or perhaps an sql query that would supply this info?
Thanks
sysnmaster:systabinfo comes to mind. Join that to systabnames to get
databaase/table names.
cheers
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
WILL LANDSTROM
Sent: Thursday, February 07, 2008 11:52 AM
To: ids@iiug.org
Subject: how to determine say top 10 tables in size? [11227]
With 9.4 is there a quick and easy way to see the top 10 (or so) biggest
tables in terms of used space? I know of various manual ways to do
this...including simply using dbaccess to display one by one..or perhaps
looking at output of dbexport or even the actual *.dat files. But is there a
sysmaster view or perhaps an sql query that would supply this info?
Thanks
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
If you regularly run update statistics - you should be able to get a good
approximation using :
select tabname, rowsize*nrows from systables order by 2 desc
(blobs + varchars will mess up the exact sizes..)
On Thursday 07 February 2008 16:51:45 WILL LANDSTROM wrote:
> With 9.4 is there a quick and easy way to see the top 10 (or so) biggest
> tables in terms of used space? I know of various manual ways to do
> this...including simply using dbaccess to display one by one..or perhaps
> looking at output of dbexport or even the actual *.dat files. But is there
> a sysmaster view or perhaps an sql query that would supply this info?
> Thanks
>
>
> ***************************************************************************
>**** Forum Note: Use "Reply" to post a response in the discussion forum.
>
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!
Select first 10 tabname, npused from systables where tabid >99 and tabtype="T"
order by npused desc;
Run at least update statstics low for the DB if you want aboslute accuracy.
If you want this to be database independent. Then
select first 10 tabname, npused from systabnames, sysptnhdr
where systabnames.partnum = sysptnhdr.partnum
order by npused desc;
The latter will include system catalog tables, and the tblspace tblspace, just
FYI.
----- Original Message ----
From: WILL LANDSTROM <willlandstrom@yahoo.com>
To: ids@iiug.org
Sent: Thursday, February 7, 2008 10:51:45 AM
Subject: how to determine say top 10 tables in size? [11227]
With
9.4
is
there
a
quick
and
easy
way
to
see
the
top
10
(or
so)
biggest
tables
in
terms
of
used
space?
I
know
of
various
manual
ways
to
do
this...including
simply
using
dbaccessto
display
one
by
one..or
perhaps
looking
at
output
of
dbexportor
even
the
actual
*.dat
files.
But
is
there
a
sysmaster
view
or
perhaps
an
sql
query
that
would
supply
this
info?
Thanks
*******************************************************************************
Forum
Note:
Use
"Reply"
to
post
a
response
in
the
discussion
forum.
See
you
at
the
IIUG
Informix
2008
Conference
The
Power
Conference
for
Informix
Professionals
April
27
-
30,
2008
Marriott
Overland
Park
(Kansas
City),
Kansas
http://www.iiug.org/conf
Registration
Now
Open!!
Mark. Thank you. This is exactly what I needed.
Jack. Thanks. This is useful info.