troubleshooting Question

ROWNUM in Oracle

Avatar of suprapto45
suprapto45Flag for Singapore asked on
Oracle DatabaseJava
14 Comments1 Solution3871 ViewsLast Modified:
Hi,

Currently, I have one problem in my J2EE application. I have one SELECT query that joins up 8-10 tables at a time and it took around 7-10 minutes (we have optimized it...it was 30-40 minutes before) to execute the query as it returns quite lot of data (>60,000 records). Now, the problem is that it "jammed up" our application server and the queues are exhausted.

Now, I was thinking to use ROWNUM in Oracle to implement the "efficient" pagination. However, it seems that although I specify the ROWNUM, the whole SELECT statement is executed first then only the defined number of records are taken out from the result of the SELECT statement. Hence, it does not solve my problem as the bottleneck is actually on the SELECT statement itself (7-10 minutes).

Hence, can anyone who expert in Java or Oracle suggest me some solid solution here?

Thanks
David
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros