[icinga-devel] [RFC] SQL Queries

Axel Liljencrantz axel.liljencrantz at freecode.no
Mon May 18 11:57:50 CEST 2009

On Sat, 2009-05-16 at 21:28 +0200, Michael Friedrich wrote:
> MySQL: LIMIT 0,1
> Postgre: LIMIT 0 OFFSET 1
> Oracle: WHERE ROWNUM BETWEEN 0 and 1
> (correct me if I'm wrong - I hate those syntax differences, it will 
> bloat the IDOUtils code a lot again)

«limit FOO offset BAR» should work in both MySQL and Postgre, so that
bit can be simplified a bit. 

The Oracle statement above will not work unless the offset number is
_always_ 0, since ROWNUM is increased _after_ the where-clause. I love
Oracle. To do limit/offset in a general way on Oracle, you have to
assign rownum to an alias in an inner select query and then filter it
out in an outer query like this:

select * from (
select rownum i_hate_my_life, ...
from ...)
where i_hate_my_life between 10 and 20

For extra brownie points, one can add this optimizer hint to the query:

/*+ FIRST_ROWS(20) */

Apparently, the Oracle Optimizer doesn't always figure out that it
doesn't need to fetch the entire set unless you do that. Never seen a
case where it made a difference, but I've seen it recommended many

Axel Liljencrantz

Konsulent, Fri Programvare / Free Software Consultant
Cell: +47 - 473 44 003
Phone: +47 - 21 53 69 00, Fax: +47 - 21 53 69 09
Addr: Nydalsveien 30b, 0484 Oslo
Web: http://www.freecode.no/

«Some people have told me they don't think a fat penguin really embodies
the grace of Linux, which just tells me they have never seen an angry
penguin charging at them in excess of 100mph. They'd be a lot more
careful about what they say if they had.» - Linus Torvalds
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
URL: <http://lists.icinga.org/pipermail/icinga-devel/attachments/20090518/12c54b85/attachment.sig>

More information about the icinga-devel mailing list