Message-Id: <s035d9b7.011@bristol21.bristol.ac>
Date: Fri, 20 Feb 2004 09:55:46 +0000
From: "Niall Litchfield" <n-litchfield@audit-commission.gov.uk>
To: <oracle-l@freelists.org>
Subject: RE: Counting number of rows
Mime-Version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
Content-Disposition: inline
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle-l@freelists.org

Comments embedded

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

Absolutely right, one always wants to know what is actually required of a r=
equest, before one answers it. Of course when the questioner doesn't know w=
hat they want (database size anyone?) there is scope for confusion. I guess=
 where I was coming from was that for large often used tables there will be=
 a cost, potentially very large indeed of execution plans change, in analyz=
ing a table 'just' to answer the question how many rows do we have. If accu=
rate[1] figures are truly required then select count(*) is the only solutio=
n available. In general though people tend to over specify accuracy for que=
stions such as these. A bit like my estate agent/realtor/land agent who sen=
d us details of a house with room sizes specified in meters to 4 decimal pl=
aces (with a disclaimer that all measures were approximate).=20

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

Well if you take a look at www.audit-commission.gov.uk you will see that my=
 employer is a public sector auditor and regulator in the UK, in addition i=
n a former life I trained as an auditor/accountant (with a well known firm =
whose name begins with K and ends with G) before they worked out I was a te=
rrible accountant and fired me, so I sort of see where you are coming from.=
 It is however a common misconception that (financial) audits are designed =
to verify the *accuracy* of accounts, they are designed to verify that they=
 give a *fair* view of the business - at least in the UK, but I believe the=
 same applies in oz. As you suggest one shouldn't hope that the figures are=
 'good enough', but one shouldn't be anal about over stating the accuracy e=
ither.=20

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

[1] there is a further problem with the select count(*) approach in oracle =
at least, which is that it will only tell you the count of rows *at the tim=
e you started the report*, if collecting these rows counts takes a long tim=
e (say in a 2tb OLTP system) then there will be a built in 'inaccuracy' unl=
ess you specify the timestamp of the report as the start of the select, not=
 the completion of the report.=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@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
-----------------------------------------------------------------