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

12 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

Roe 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

Farrah said...

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

4d ultrasounds

highheels said...

Christian Louboutin
As a way to design and style remarkably one of a kind and effective Christian Louboutin Petal Sandals; they consider benefit of your very last equipment and technologies in order that you’ll be fully capable of get the brightest and designer sandal designs from them in way. Seems wise, the Christian Louboutin Petal Sandals are extremely inimitable and flexible shoes in an attempt to seize your attentions directly. On the subject of the colours, the monumental fascination is that they use superlative colors for making fancy your Christian Louboutin Petal Sandals.

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

admin said...

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

saim said...

Interesting post !!! i love to see it, when people need such type of site. which has great information about this topic... Great for new generation..
Thanks..


find doctor list

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

0Zero7 said...

Nice bikes but its quite pricey.But also it depends what kind of touring or activities you plan to do.
web hosting Pakistan

Facebook Descargar said...

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