RE: Counting number of rows

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 20 Feb 2004 09:55:46 +0000
Message-Id: <s035d9b7.011@bristol21.bristol.ac>

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_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 Fri Feb 20 2004 - 03:55:46 CST