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.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

this question gives some pretty good information