RE: Counting number of rows

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Fri, 20 Feb 2004 14:15:22 +1100
Message-ID: <OFABFE0046.48DCF44F-ONCA256E40.00111950@transurban.com.au>

I would think that determining "how accurate" the figure is expected to be would be a first important step. I don't think you can ever "hope it is good enough" without confirming that fact - even your example below rounds off 2 per-cent which may or may not be acceptable. Likewise, I realise there are many situations where it would be more than accurate enough.

I guess I only reacted because your email address mentions the "audit-commission" and I don't normally associate the words "audit" and "good enough" with each other. :-)

                                                                                                                                                
                      "Niall Litchfield"                                                                                                        
                      <n-litchfield_at_audit-commiss        To:       <oracle-l_at_freelists.org>                                                     
                      ion.gov.uk>                        cc:                                                                                    
                      Sent by:                           Subject:  RE: Counting number of rows                                                  
                      oracle-l-bounce_at_freelists.o                                                                                               
                      rg                                                                                                                        
                                                                                                                                                
                                                                                                                                                
                      18/02/2004 20:47                                                                                                          
                      Please respond to oracle-l

I'd kind've like to think that anyone who asks for the number of rows in a =
table, for example for measuring how many financial transactions we've done=
over time wants the figure to be reliable, but not accurate, for example i=
t is pretty unlikely that these sorts of queries care that you have done 7,=
125,987 transactions in the last year, but 7million would be a fair reflect=
ion. If this holds then they probably only want the same sort of accuracy a=
bout num_rows as you would like the optimizer to use when determining execu=
tion plans. In other words in a well run db and if asked reasonable questio=
ns I'd hope num_rows was good enough.=20

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=20

> -----Original Message-----
> From: mladen_at_wangtrading.com=20
> Sent: 17 February 2004 21:54
> To: mladen_at_wangtrading.com; oracle-l_at_freelists.org
> Subject: Re: Counting number of rows

>=20
>=20

> That may not be completely accurate. The accuracy of the NUM_ROWS =20
> number is dependent upon the time when statistics was=20
> generated. It the
> table in question is a high=3Dtraffic transactional table, and=20
> statistics
> was generated 2 weeks ago, you may miss the number by millions.

>=20

> On 02/17/2004 04:41:17 PM, Ryan wrote:
> > if the table is analyzed just go to dba_Tables.num_rows
> >=20
> > bitmap indexes are very fast on counts.
> > ----- Original Message -----
> > From: "Mladen Gogala" <mladen_at_wangtrading.com>
> > To: <oracle-l_at_freelists.org>
> > Sent: Tuesday, February 17, 2004 4:15 PM
> > Subject: Re: Counting number of rows
> >=20
> >=20
> > > The function below would count rows extremely quickly, and is
> > > independent of the table size. Even better, it is completely
> > accurate
> > > and doesn't rely on the underlying statistics. The table
> > > in question may not even be analyzed. It does have one minor
> > > shortcoming, however.
> > >
> > > <PRANK>
> > > create or replace
> > > function count_rows(tbl in varchar2) return number deterministic
> > > as
> > > TRUNC VARCHAR2(128):=3D'truncate table '||tbl;
> > > begin
> > > execute immediate trunc;
> > > return(0);
> > > end;
> > > /
> > > </PRANK>
> > > On 02/17/2004 03:27:31 PM, "Potluri, Venu (CT Appl Suppt)" wrote:
> > > > Is there a quick way to count number of rows in a table? Don't
> > want
> > > > to
> > > > do select count(*) from..... on a table (such as GL_BALANCES) =20
> > with
> > > > more than 250 million rows.
> > > > --------------------------------------------------------
> > > >
> > > > If you are not an intended recipient of this e-mail, please =20
> > notify
> > > > the
> > > > sender, delete it and do not read, act upon, print, disclose,
> > copy,
> > > > retain or redistribute it. Click here for important additional
> > terms
> > > > relating to this e-mail. http://www.ml.com/email_terms/
> > > > --------------------------------------------------------
> > > >
> > > >
> >=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > =3D=3D
> > > >
> > > > If you are not an intended recipient of this e-mail, please =20
> > notify
> > > > the sender, delete it and do not read, act upon, print,=20
> disclose,
> > > > copy, retain or redistribute it.
> > > >
> > > > Click here for important additional terms relating to this e-=20
> > mail.
> > > > <http://www.ml.com/email_terms/>
> > > >
> > > >
> >=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > =3D=3D
> > > >
> > > > ----------------------------------------------------------------
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > ----------------------------------------------------------------
> > > > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > > > put 'unsubscribe' in the subject line.
> > > > --
> > > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > > >=20
> -----------------------------------------------------------------
> > > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at http://www.freelists.org/archives/oracle-l/
> > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> >=20
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >=20
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>=20
>=20

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------





<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Feb 19 2004 - 21:15:22 CST