Sort the tables in a database by size
Liviu Lintes wanted to sort the tables in a database by size to find one around 10 MB, since oncheck -pT was impractical. Reinhard Habichtsberg suggested joining systabnames and systabinfos in sysmaster (ti_rowsize, ti_nrows). John Carlson gave a sysmaster query on sysptprof and sysptnhdr (nptotal, npused). Michael Krzepkowski suggested row length times nrows from systables after update statistics. No confirmation from the poster.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi,
I want to test a 10 MB table. The database has lots of tables. I can find
the size of a table running oncheck -pT. But this is not useful when you
want to select from hundreds or thousands of tables one which is around 10MB
size. So my question is :
1. Does anybody knows a way of sorting the tables in a database by size ?
Thanks
Liviu
Liviu Lintes <liviu@garpac.com> wrote:
> Hi,
> I want to test a 10 MB table. The database has lots of tables. I
can find
> the size of a table running oncheck -pT. But this is not useful
when you
> want to select from hundreds or thousands of tables one which is
around 10MB
> size. So my question is :
> 1. Does anybody knows a way of sorting the tables in a database by
size ?
>
> Thanks
> Liviu
>
>
You could do something with sed and awk and sort on the
output of oncheck --pT.
An other suggestion:
Have a look to the sysmaster (pseudo-)database. You may join
the tables systabnames and systabinfos(partnum, ti_partnum).
In systabinfos you´ll see the columns ti_rowsize and ti_nrows.
Some simple selects and you have it.
I don´t know if it is documented.
Regards,
Reinhard
As it appears that you're using IDS 7.x, try this . . .
database sysmaster;
select dbsname, tabname, nptotal, npused
from sysptprof prof, sysptnhdr hdr
where prof.partnum = hdr.partnum
Hopefully this will get you in the right direction.
John Carlson
Informix DBA
WHSmith USA
Liviu Lintes wrote:
>
> Hi,
> I want to test a 10 MB table. The database has lots of tables. I can find
> the size of a table running oncheck -pT. But this is not useful when you
> want to select from hundreds or thousands of tables one which is around 10MB
> size. So my question is :
> 1. Does anybody knows a way of sorting the tables in a database by size ?
>
> Thanks
> Liviu
HI!
After you run update statistics you can find info in systables: row length *
nrows.
This will not include index size, but should give you a good idea.
Just use select tabname and the expression from systables.
HTH
Michael
Liviu Lintes wrote:
> Hi,
> I want to test a 10 MB table. The database has lots of tables. I can find
> the size of a table running oncheck -pT. But this is not useful when you
> want to select from hundreds or thousands of tables one which is around 10MB
> size. So my question is :
> 1. Does anybody knows a way of sorting the tables in a database by size ?
>
> Thanks
> Liviu