471,075 Members | 815 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

selecting ROWNUM causes query to take forever

I have a query that takes about two minutes and returns 97 rows.
If I change the query only by adding ROWNUM to the outermost
select clause, the query never returns (I let it run overnight).
I'm not using ROWNUM in the where clause, I'm just selecting it.
I can't think of any reason that just selecting ROWNUM would make any
difference. This is Orace 8.1

I spent a day searching Google groups without finding anything on this.

Any ideas?

Thanks in advance,

Paul
Jul 19 '05 #1
2 3809
Paul Bradford wrote:
I have a query that takes about two minutes and returns 97 rows.
If I change the query only by adding ROWNUM to the outermost
select clause, the query never returns (I let it run overnight).
I'm not using ROWNUM in the where clause, I'm just selecting it.
I can't think of any reason that just selecting ROWNUM would make any
difference. This is Orace 8.1

I spent a day searching Google groups without finding anything on this.

Any ideas?

Thanks in advance,

Paul

Crystal ball is in the repair shop. Why not post the query, explain
plan, etc?

--
Regards, Frank van Bortel

Jul 19 '05 #2
> Paul Bradford wrote:
I have a query that takes about two minutes and returns 97 rows.
If I change the query only by adding ROWNUM to the outermost
select clause, the query never returns (I let it run overnight).
I'm not using ROWNUM in the where clause, I'm just selecting it.
I can't think of any reason that just selecting ROWNUM would make any
difference.


For reasons I don't understand, adding ROWNUM caused a different
(and worse) execution plan. After I added some hints to get a good
execution plan, my problem is solved.
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Kevin Crosbie | last post: by
8 posts views Thread by Agoston Bejo | last post: by
4 posts views Thread by pierig.gueguen | last post: by
5 posts views Thread by uthuras | last post: by
2 posts views Thread by Paul Bradford | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.