Re: SELECT COUNT(APPROXIMATELY *) FROM a WHERE b=c;

I believe what Scott is trying to say is: The moment the information is returned to the user, the information is outdated. Because after the "count" transaction was started, new rows may have been added which are not seen by the query.

Maybe the "approximately" in the web page covers for this fact and maybe it doesn't.

If you would like to work an estimate yourself, you could do something like this:
:sample_size := 1000;
select count(*) into x from yourtable where (your condition) AND ROWNUM < sample_size;
select num_rows * (x / 1000) into z from user_tables where table_name='yourtable' ;

and z would hold your statistically approximate value. For what it's worth.

Syntax above may not be good enough for compiling. I still have to work on my PLSQL skills.

HTH

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address

"Daniel Morgan" <dmorgan_at_exesolutions.com> a écrit dans le message de news:
3D04CC59.9A024195_at_exesolutions.com...


> Scott Mattes wrote:

>

> > One reason might be that between the time the count ends and the result

is


> > displayed any number of other transactions will/might have occurred that

> > Inserted more records that could match the select or records that

matched


> > were Deleted.

> >

> > "Monty" <mmontreaux_at_hotmail.com> wrote in message

> > news:6284dd3.0206100532.24373c8f_at_posting.google.com...

> > > Hello, every now and then I go to a website, perform a search, and it

> > > comes back and says "found APPROXIMATELY ? records".

> > >

> > > A case in point is "http://asktom.oracle.com" (click on

> > > Search/Archives, type in something in the search box, and press go)

> > > and I am probably not wrong in assuming there is an Oracle database

> > > behind this web site.

> > >

> > > How can an SQL search be formulated to find approximately the number

> > > of hits (which I'm assuming is just a quick preview of a very large

> > > database with a query before actually doing the 'real' query) ?

> > >

> > > Monty

>

> I'm not sure what is going on with respect to what is described here.

>

> But, in Oracle, one a query has begun ... no amount of inserts or deletes

can


> affect the query result. Not even truncating the table.

>

> Daniel Morgan

>