why there's a difference of performance gain among both these queries?

Question

SELECT instmax,
r
FROM
(SELECT instmax,
rownum r
FROM
( SELECT instmax FROM pswlinstmax ORDER BY instmax DESC NULLS LAST
)
WHERE rownum <= 10
)
WHERE r >=6;

Output

SELECT instmax,
r
FROM
(SELECT instmax,
rownum r
FROM
( SELECT instmax FROM pswlinstmax ORDER BY instmax DESC NULLS LAST
)
)
WHERE r between 6 and 10;

Output

Is there really a definite performance gain among both the query?Can you please clarify me on this?

Answer 1

Hi!

That's probably because the STOPKEY optimization doesn't work anymore.

The "rownum < xx" is very special (called Top-N query) that has special optimizations. the between clause doesn't trigger that anymore.

You should compare the exeuction plans and look for "STOPKEY", if that is in, it is the optimized Top-N query.

see also:

http://blog.fatalmind.com/2010/07/30/analytic-top-n-queries/

Answer 2

Oracle cannot push conditions that involve aliased ROWNUM into the inline views.

This means that the second query will use a full table (or index) scan with filtering on rn, while the first one will use STOPKEY (since it uses unaliased ROWNUM < 10)

You may want to read this article:

• Oracle: ROW_NUMBER vs ROWNUM