Monday, June 5

SQL_CALC_FOUND_ROWS in Oracle

People keep telling me about all these new fancy features MySQL have and how nice it would be if Oracle had the same. My usuall response is "Uhm, had that for 15 years or so". Oracle just call the feature something difrerent. My latest MySQL "fancy feature" question was about SQL_CALC_FOUND_ROWS.

Here's how you get the same functionality it in Oracle
select found_rows, empno, job from 
(select count(*) over () found_rows, empno, job, rownum rn
from emp order by empno)
where
rn between 1 and 5
/
FOUND_ROWS EMPNO JOB
---------- ---------- ---------
15 7369 CLERK
15 7499 SALESMAN
15 7521 SALESMAN
15 7566 MANAGER
15 7654 SALESMAN

You do get a bit more data transfered to the application layer, one value for each column. To avoid this and only get the count on the first row you can modify the inner query to use decode() like this
select decode(rownum,1,count(*) over ()), empno, job 
from emp order by empno

9 comments:

Anonymous said...

FOUND_ROWS doesn't work in my version of Oracle, 10g. Can you tell please what version did that code work for you ? Or how shall it be in Oracle 10g ?

Anonymous said...

Perfectly true but there is an enormous difference: perfomances!

Doing your method you need to do a sub query that is executed for every query and this is killing for every kind of system, while mysql do this trasparently counting only founded records with a big speed up!

Oracle is better of mysql talking of features but a lot of things now are supported by mysql but this is alot faster than oracle, do speed tests and look it by yourself

snafu918 said...

Ok so I have to do pagination with a huge where clause and an order by function that will change. Currently I'm using:
row_number() over (order by b)

But because I am limiting the number of rows returned I have a 3 deep sql query that is overly complex.

Here's a psuedo code example of what I'm doing that has been dumbed down. Notice the repeat of the where statement:

SELECT *
FROM
(
SELECT rownumber() over (ORDER BY lastName) as myRowNum,
(
SELECT Count(*)
FROM instancename.someTable s
WHERE s.column = ?
AND s.column1 = ?
AND s.column2 = ?
AND s.column3 = ?
AND s.column4 = ?
AND s.column5 = ?
AND s.column6 = ?
AND s.column7 = ?
AND s.column8 = ?
AND s.column9 is null
AND s.column10 = ?
) as totalrecords,
s.*
FROM instancename.someTable s
WHERE s.column = ?
AND s.column1 = ?
AND s.column2 = ?
AND s.column3 = ?
AND s.column4 = ?
AND s.column5 = ?
AND s.column6 = ?
AND s.column7 = ?
AND s.column8 = ?
AND s.column9 is null
AND s.column10 = ?
)
WHERE myRowNum BETWEEN 1 AND 10



I believe that if I was using MySQL instead of Oracle I wouldn't need that nasty code, unless you have a different more elegant solution which I don't doubt because I am not a sql developer by trade.

of course unless I'm wrong that same query would look like this in mysql:

SELECT SQL_CALC_FOUND_ROWS as totalrecords,s.*
FROM instancename.someTable s
WHERE s.column = ?
AND s.column1 = ?
AND s.column2 = ?
AND s.column3 = ?
AND s.column4 = ?
AND s.column5 = ?
AND s.column6 = ?
AND s.column7 = ?
AND s.column8 = ?
AND s.column9 is null
AND s.column10 = ?
LIMIT 0, 10

Fara Fae said...

Most what i read online is trash and copy paste but i think you offer something different. Keep it like this...

4d ultrasounds

Anna said...

I liked the posts and cool layout you have here! I would like to thank you for sharing your experience and the time it took to post!! Two Thumbs up!

doctor ratings and reviews | find doctor list | doctor reviews by patients

Sharon said...

Nice review. I couldn't say it better myself. top fashion icons

Anna said...

i have read your blog and find that your articles are amazing, i have added this into my bookmark. Thanks a lot. ~^- organic supplements -^~

Sabrina Taylor said...

Yea, this is really a fascinating blog, lots of stuff that I can get into. One thing I just want to say is that you’re Blog is so perfect…thanks!!!!

Best Graphic Design Site

Unknown said...

Very interesting! Thanks you
Signature:
download descargar facebook messenger and download free descargar facebook para android , descargar facebook gratis , descarga facebook