Get Total Count Of Rows In Oracle And Then Get Only 4 Results

I am building a search engine, therefore, as in google, I am displaying only 4 results but I also need the total number of matched results. Can I do in a single query in ORACLE?

Solution 1:

Use window functions:

select*from (
  select col1, 
         col2,
         row_number() over (orderby some_column) as rn,
         count(*) over () as total_count
  from the_table
) 
where rn <=4;

But if that table is really big, it is not going to be very fast.

Solution 2:

You can do something like below;

ready to run query:

SELECT tbl2.*FROM (SELECT tbl1.*, ROWNUM rownumber
          FROM (SELECT1, 2, count(*) FROM DUAL) tbl1) tbl2
 WHERE tbl2.rownumber BETWEEN0AND4;

And the result is:

column1|column2|COUNT(*)|ROWNUMBER
   1211

This gets the rows between 0 and 4. So if you want to get others, you can modify to get inputs for those values instead.

Solution 3:

From 12c, you can avoid the RANK method, and use the TOP-N query directly :

SQL>SELECT*2FROM3    ( SELECT object_name,
  4COUNT(*) OVER () AS total_count FROM all_objects
  5    )
  6FETCHFIRST5ROWSONLY7/

OBJECT_NAME TOTAL_COUNT
----------- -----------
I_OBJ1            89738
CLU$              89738
I_COL3            89738
I_UNDO1           89738
I_CDEF4           89738SQL>

Solution 4:

It won't be using a single SQL statement, but check out the APPROX_COUNT_DISTINCT() function, which is designed to be a very quick approximate COUNT for use in things like search engines where the exact number of results may not be essential.

http://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBJEAJE