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

"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.

I suspect - though I do not know - that Tom is being pedantic about the accuracy of the search. Ask Tom operates using IIRC Intermedia Indexes not plain and simple Oracle indexes. One of the characteristics of such indexes is that they are out of date. Thus a search is by definition not guaranteed to return all results.

Even if this is not the case , this article http://shrinkalink.com/217 makes it clear that Ask Tom requeries for extra pages. In this case Daniel's objection - that the result of a query is fixed at the start of the query does not apply since the query for the second page has not been run yet.

Hope this helps rather than confuses.

>
> 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) ?

In short you can't, though using either or both of the techniques above you might wish to inform your users of the somewhat provisional nature of their result sets in your application.

--
Niall Litchfield
Oracle DBA
Audit Commission UK