Hi Patrick,
> Well Keith, that's impressive. Can you also post some benchmarks? ;)
Bear in mind that these results are running on my local machine,
through PL/SQL Developer which counts the time it takes to display the
result rows as part of the query timing. The REF CURSOR results
should be a bit more accurate, though, once again on an untuned local
development machine. The ROW_NUMBER() query is so expensive because
it was called as dynamic SQL. However, once it is cached (after the
first call, it should get faster).
-- Executed in 1.562s (in pl/sql Developer) on local machine
-- Executed in 0.046s in PL/SQL block using a REF CURSOR
SELECT *
FROM (SELECT owner,
object_name,
object_id,
object_type,
ROW_NUMBER() OVER (ORDER BY OBJECT_TYPE ASC) AS rn
FROM all_objects -- ~23000 rows in this table
) inn
WHERE rn BETWEEN 350 AND 500
-- Executed in 1.031s (in pl/sql Developer) on local machine
-- Executed in 0.015s in PL/SQL block using a REF CURSOR
SELECT *
FROM (SELECT inn.*,
ROWNUM rn
FROM (SELECT owner,
object_name,
object_id,
object_type
FROM all_objects -- ~23000 rows in this table
ORDER BY object_name) inn
) outt
WHERE outt.rn BETWEEN 350 AND 500;
-- Executed in 0.719s (in pl/sql Developer) on local machine
-- Executed in 0.016s in PL/SQL block using a REF CURSOR
SELECT *
FROM (SELECT owner,
object_name,
object_id,
object_type,
ROWNUM AS rn
FROM all_objects -- ~23000 rows in this table
) inn
WHERE rn BETWEEN 350 AND 500;
I hope this is informative for you.
Cheers,
Keith.
Community email addresses:
Post message: [email protected]
Subscribe: [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
List owner: [EMAIL PROTECTED]
Shortcut URL to this page:
http://groups.yahoo.com/group/php-list
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php-list/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
