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
-----------------------------------------------------------------
Received on Wed Feb 18 2004 - 03:47:15 CST
