Row Count
Paul wanted row counts for many tables without running SELECT COUNT(*) on each. Replies: systables.nrows in the local database gives counts, but only as of the last UPDATE STATISTICS; sysmaster's sysptnhdr holds live row counts, with the caveat that partitions can be tables, indexes or fragments. Art Kagel supplied a join of systabnames and sysptnhdr with SUM(nrows) grouped by dbsname/tabname to handle fragmented tables (corrected to st.partnum = sp.partnum). Others noted COUNT(*) is actually fast in Informix, even on huge tables, and is the only guaranteed-accurate method, with a shell loop offered to script it.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All, Is there a way from the sysmaster database i can be able to tell the number of rows in different tables in my database? I don't want to do "Select count(*) from mytable" it s lots of work!! Paul
You can from your local database,
Select nrows from systables
Where tabname = "table you want the row count for";
However, this is only as the last update statistics, "select count(*) from
table" is more accurate
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL
GATHOGO
Sent: 28 July 2008 08:54 AM
To: ids@iiug.org
Subject: Row Count [12917]
All,
Is there a way from the sysmaster database i can be able to tell the number
of rows in different tables in my database? I don't want to do "Select
count(*) from mytable" it s lots of work!!
Paul
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
sysptnhdr has the number of rows, but you have to be careful, because a
"partition" can be a table, an index or a fragment.
Nevertheless, "select count(*) from table" is the only way to be sure... the
rest can change without notice...
Regards.
On Mon, Jul 28, 2008 at 8:05 AM, Mark Tyrer <mark.tyrer@rtt.co.za> wrote:
> You can from your local database,
>
> Select nrows from systables
> Where tabname = "table you want the row count for";>
> However, this is only as the last update statistics, "select count(*) from
> table" is more accurate
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PAUL
> GATHOGO
> Sent: 28 July 2008 08:54 AM
> To: ids@iiug.org
> Subject: Row Count [12917]
>
> All,
>
> Is there a way from the sysmaster database i can be able to tell the number
> of rows in different tables in my database? I don't want to do "Select
> count(*) from mytable" it s lots of work!!
>
> Paul
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
PAUL GATHOGO schrieb:
> All,
>
> Is there a way from the sysmaster database i can be able to tell the number
of
> rows in different tables in my database? I don't want to do "Select count(*)
> from mytable" it s lots of work!!
>
> Paul
>
Paul, it is not that hard and SELECT COUNT(*) is very fast, needs almost
no time.
This will give you a list of your tables (including sys* tables and the
creation date
using the System catalogue Tables in your database), vary it as you want:
echo "OUTPUT TO PIPE 'grep -v \\\\"^$\\\\"' WITHOUT HEADINGS SELECT tabname[1,35],
created FROM
SYSTABLES;" | dbaccess <put name of your database here> 2>/dev/null
Given you have a list of table names to count in a file
'/tmp/count_these_tables'
and you use ksh or bash:
for TN in $( cat /tmp/count_these_tables ); do
echo -n "${TN}|"
echo "OUTPUT TO PIPE 'grep -v \\\\"^$\\\\"' WITHOUT HEADINGS SELECT COUNT(*) FROM
$TN;" \\\\
| dbaccess <name of database here> 2>/dev/null
done
will give you output ready to load / use in spreadsheet
Lines will look like:
test_table_01| 1332420 |
HTH,
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
Paul,
a select count(*) is not a hard work in informix, you can use with no problem.
I have tables with more than 1,5 billion and i use it with no problem.
select count(*) is hark work in oracle or sqlserver. it works different ininformix.
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de PAUL
GATHOGO
Enviada em: segunda-feira, 28 de julho de 2008 03:54
Para: ids@iiug.org
Assunto: Row Count [12917]
All,
Is there a way from the sysmaster database i can be able to tell the number of
rows in different tables in my database? I don't want to do "Select count(*)
from mytable" it s lots of work!!
Paul
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
select dbsname, tabname, sum(nrows)
from systabnames st, sysptnhdr sp
where st.partnum = sysptnhdr.partnum
and dbsname = "mydatabase"
and tabname = "mytablename"
group by dbsname, tabname;
You can skip the sum() and group by if you don't have any fragmented tables.
Art
On Mon, Jul 28, 2008 at 2:53 AM, PAUL GATHOGO <pgathogo@gmail.com> wrote:
> All,
>
> Is there a way from the sysmaster database i can be able to tell the number
> of
> rows in different tables in my database? I don't want to do "Select
> count(*)
> from mytable" it s lots of work!!
>
> Paul
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
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.
Great query Art I was recently asked for a similar result set and you saved me
some work. Thanks. Slight syntax error though should be sp.partnum in the
where clause.
Zev Berezin
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, July 28, 2008 7:38 AM
To: ids@iiug.org
Subject: Re: Row Count [12922]
select dbsname, tabname, sum(nrows)
from systabnames st, sysptnhdr sp
where st.partnum = sysptnhdr.partnum
and dbsname = "mydatabase"
and tabname = "mytablename"
group by dbsname, tabname;
You can skip the sum() and group by if you don't have any fragmented tables.
Art
On Mon, Jul 28, 2008 at 2:53 AM, PAUL GATHOGO <pgathogo@gmail.com> wrote:
> All,
>
> Is there a way from the sysmaster database i can be able to tell the number
> of
> rows in different tables in my database? I don't want to do "Select
> count(*)
> from mytable" it s lots of work!!
>
> Paul
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
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.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Yep, st.partnum = sp.partnum
Thanks, Zev.
Art
On Mon, Jul 28, 2008 at 12:18 PM, Zev Berezin <zevb@bhphoto.com> wrote:
> Great query Art I was recently asked for a similar result set and you saved
> me
> some work. Thanks. Slight syntax error though should be sp.partnum in the
> where clause.
>
> Zev Berezin
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, July 28, 2008 7:38 AM
> To: ids@iiug.org
> Subject: Re: Row Count [12922]
>
> select dbsname, tabname, sum(nrows)
> from systabnames st, sysptnhdr sp
> where st.partnum = sysptnhdr.partnum
> and dbsname = "mydatabase"
> and tabname = "mytablename"
> group by dbsname, tabname;>
> You can skip the sum() and group by if you don't have any fragmented
> tables.
>
> Art
>
> On Mon, Jul 28, 2008 at 2:53 AM, PAUL GATHOGO <pgathogo@gmail.com> wrote:
>
> > All,
> >
> > Is there a way from the sysmaster database i can be able to tell the
> number
> > of
> > rows in different tables in my database? I don't want to do "Select
> > count(*)
> > from mytable" it s lots of work!!
> >
> > Paul
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> 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.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
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.