Vague count in sql select statements

Question

I guess this has been asked in the site before but I can't find it.

I've seen in some sites that there is a vague count over the results of a search. For example, here in stackoverflow, when you search a question, it says +5000 results (sometimes), in gmail, when you search by keywords, it says "hundreds" and in google it says aprox X results. Is this just a way to show the user an easy-to-understand-a-huge-number? or this is actually a fast way to count results that can be used in a database [I'm learning Oracle at the moment 10g version]? something like "hey, if you get more than 1k results, just stop and tell me there are more than 1k".

Thanks

PS. I'm new to databases.

good question. i'm looking forward to answers too – Dario Pedol May 03 '12 at 15:27 — Dario Pedol, May 03 '12 at 15:27

score 3 · Answer 1 · answered May 03 '12 at 15:31

3

Usually this is just a nice way to display a number.

I don't believe there is a way to do what you are asking for in SQL - count does not have an option for counting up until some number.

I also would not assume this is coming from SQL in either gmail, or stackoverflow. Most search engines will return a total number of matches to a search, and then let you page through results.

As for making an exact number more human readable, here is an example from Rails:

http://api.rubyonrails.org/classes/ActionView/Helpers/NumberHelper.html#method-i-number_to_human

answered May 03 '12 at 15:31

Andrew Kuklewicz

10,621
1
34
42

Stack Overflow is running on SQL Server (as far as I know) and [makes a lot of use of SQL](http://data.stackexchange.com/stackoverflow/queries). So I'm not sure why you'd claim that this shouldn't be done with SQL? – Lukas Eder May 03 '12 at 15:50
1

I said don't assume it is using SQL, not that you shouldn't use SQL. And actually, SO uses lucene.net: http://blog.stackoverflow.com/2011/01/stack-overflow-search-now-81-less-crappy/ – Andrew Kuklewicz May 03 '12 at 16:50

score 2 · Answer 2 · edited May 23 '17 at 12:11

With Oracle, you can always resort to analytical functions in order to calculate the exact number of rows about to be returned. This is an example of such a query:

SELECT inner.*, MAX(ROWNUM) OVER(PARTITION BY 1) as TOTAL_ROWS
FROM (
  [... your own, sorted search query ...]
) inner

This will give you the total number of rows for your specific subquery. When you want to apply paging as well, you can further wrap these SQL parts as such:

SELECT outer.* FROM (
  SELECT * FROM (
    SELECT inner.*,ROWNUM as RNUM, MAX(ROWNUM) OVER(PARTITION BY 1) as TOTAL_ROWS
    FROM (
      [... your own, sorted search query ...]
    ) inner
  ) 
  WHERE ROWNUM < :max_row
) outer
WHERE outer.RNUM > :min_row

Replace min_row and max_row by meaningful values. But beware that calculating the exact number of rows can be expensive when you're not filtering using UNIQUE SCAN or relatively narrow RANGE SCAN operations on indexes. Read more about this here: Speed of paged queries in Oracle

As others have said, you can always have an absolute upper limit, such as 5000 to your query using a ROWNUM <= 5000 filter and then just indicate that there are more than 5000+ results. Note that Oracle can be very good at optimising queries when you apply ROWNUM filtering. Find some info on that subject here:

http://www.dba-oracle.com/t_sql_tuning_rownum_equals_one.htm

score 1 · Answer 3 · edited May 03 '12 at 15:32

1

I don't know how fast this will run, but you can try:

SELECT NULL FROM your_tables WHERE your_condition AND ROWNUM <= 1001

If count of rows in result will equals to 1001 then total count of records will > 1000.

edited May 03 '12 at 15:32

Lukas Eder

211,314
129
689
1,509

answered May 03 '12 at 15:27

Pavel Strakhov

39,123
5
88
127

score 1 · Answer 4 · edited May 03 '12 at 15:30

1

Vague count is a buffer which will be displayed promptly. If user wants to see more results then he can request more.

It's a performance facility, after displaying the results the sites like google keep searching for more results.

edited May 03 '12 at 15:30

EBarr

11,826
7
63
85

answered May 03 '12 at 15:29

Romil Kumar Jain

20,239
9
63
92

score 1 · Answer 5 · answered May 03 '12 at 15:49

1

this question gives some pretty good information

answered May 03 '12 at 15:49

Martijn

11,964
12
50
96

score 0 · Answer 6 · edited May 20 '13 at 07:30

When you do an SQL query you can set a

 LIMIT 0, 100

for example and you will only get the first hundred answers. so you can then print to your viewer that there are 100+ answers to their request.

For google I couldn't say if they really know there is more than 27'000'000'000 answer to a request but I believe they really do know. There are some standard request that have results stored and where the update is done in the background.

Vague count in sql select statements

6 Answers6