| Using ROW_NUMBER() reduces performance [message #481904] |
Mon, 08 November 2010 04:07  |
antenc
Messages: 13
Registered: September 2009
Location: UK
|
Junior Member |
|
|
Hi there,
Can anyone provide any assistance on the following:
SELECT
person_urn,
person_title,
person_surname
FROM
(SELECT
person_urn,
person_title,
person_surname,
ROW_NUMBER() OVER (ORDER BY person_surname) R
FROM
person_int)
WHERE
R BETWEEN 0 and 20;
The query above allows me to return 20 records at a time (the query has been simplified somewhat), but by using ROW_NUMBER() the performance has been reduced quite significantly. I checked the explain plan and it means doing a FULL TABLE SCAN to return the records whereas before it was using indexes.
I had originally thought that I could use a function based index on person_surname but that's not the case. Has anyone had a similar issue, or can offer any assistance here?
Thanks in advance,
Carl
|
|
|
|
|
|
| Re: Using ROW_NUMBER() reduces performance [message #481907 is a reply to message #481905] |
Mon, 08 November 2010 04:36  |
antenc
Messages: 13
Registered: September 2009
Location: UK
|
Junior Member |
|
|
I did have rownum in the original query when I was building it up from scratch. However, my app needs to be able to select the next 20 records i.e. 20-40, 40-60 ... and so on.
The relevant parts of the sqltracing file are:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 21 0.95 4.52 13716 16572 480 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.95 4.52 13716 16572 480 20
252415 TABLE ACCESS MODE: ANALYZED (FULL) OF 'PERSON_INT'
(TABLE)
20 TABLE ACCESS FULL PERSON_INT (cr=5 pr=5 pw=0 time=7557 us)
|
|
|
|
|
|
|
|
| Re: Using ROW_NUMBER() reduces performance [message #481910 is a reply to message #481908] |
Mon, 08 November 2010 05:03 |
antenc
Messages: 13
Registered: September 2009
Location: UK
|
Junior Member |
|
|
Cookiemonster, thanks very much for the link to asktom. I also apologise for not following the format guide. I was in a bit of a rush to find a solution to my problem. I will make sure I follow the guidelines on future posts.
Flyboy, you had linked to the same post, so thanks for your reply.
Cheers,
Carl
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
