ROWNUM in Oracle
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
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
Is it possible to precalculate the result and store it in a table, so that you can request for a subset of the result from the front end, rather than running the query each time?
as it is said in your previous question, to retrieve say first 70 records you can add a condition "Where rownum < 70" and for the next 70 records change the where clause to "rownum > 70 and rownum < 140"
I don't think jdbc provide you such facility
I don't think jdbc provide you such facility
ASKER
Thanks guys.
>>"Is it possible to precalculate the result and store it in a table, so that you can request for a subset of the result from the front end, rather than running the query each time?"
Unfortunately, it cant. The query will be very dynamic that it will always be changing among the users so it really is not applicable but thanks for your opinion.
>>"as it is said in your previous question, to retrieve say first 70 records you can add a condition "Where rownum < 70" and for the next 70 records change the where clause to "rownum > 70 and rownum < 140""
Yes but that's the problem. Although I have "rownum > 70 and rownum < 140", the query is still slow because I think it needs to execute the whole SQL statement first and then retrieve "rownum > 70 and rownum < 140".
I will be off from office now. I will be back either tonight (next few hours time) or tomorrow.
Thanks for the responses.
David
>>"Is it possible to precalculate the result and store it in a table, so that you can request for a subset of the result from the front end, rather than running the query each time?"
Unfortunately, it cant. The query will be very dynamic that it will always be changing among the users so it really is not applicable but thanks for your opinion.
>>"as it is said in your previous question, to retrieve say first 70 records you can add a condition "Where rownum < 70" and for the next 70 records change the where clause to "rownum > 70 and rownum < 140""
Yes but that's the problem. Although I have "rownum > 70 and rownum < 140", the query is still slow because I think it needs to execute the whole SQL statement first and then retrieve "rownum > 70 and rownum < 140".
I will be off from office now. I will be back either tonight (next few hours time) or tomorrow.
Thanks for the responses.
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
markgeer,
I said the query speed is database issue not pagination!
I said the query speed is database issue not pagination!
To mnrz:
Yes, I agree with you that query speed is a database issue. But this question seems to be about two issues: query speed (or slowness) plus pagination. My point is that query speed is something that can be addressed in Oracle (SQL), but pagination is not.
Yes, I agree with you that query speed is a database issue. But this question seems to be about two issues: query speed (or slowness) plus pagination. My point is that query speed is something that can be addressed in Oracle (SQL), but pagination is not.
ASKER
Thanks all for the responses.
markgeer, I totally agree with your statement of
>>"The only exception to this is if you use nested selects with an "order by" in the inner select and ROWNUM in the outer select, but this does force Oracle to retrieve *ALL* of the records for the inner query first."
I think that it is the main root of my problem. I used the nested selects with an "order by" in the inner select and ROWNUM in the outer select. And as Oracle must return all the inner query first, this take long time to finish and hence the execution time is the same.
Hence, in conclusion, can I say that to improve the execution time of my application, I need to tune up my select statement?
Thanks
David
markgeer, I totally agree with your statement of
>>"The only exception to this is if you use nested selects with an "order by" in the inner select and ROWNUM in the outer select, but this does force Oracle to retrieve *ALL* of the records for the inner query first."
I think that it is the main root of my problem. I used the nested selects with an "order by" in the inner select and ROWNUM in the outer select. And as Oracle must return all the inner query first, this take long time to finish and hence the execution time is the same.
Hence, in conclusion, can I say that to improve the execution time of my application, I need to tune up my select statement?
Thanks
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks mnrz,
I will definitely look into them but you know, I am sucks at DB :). I will keep this thread open for few more hours and I will split the points based on the contribution.
Thanks again.
David
I will definitely look into them but you know, I am sucks at DB :). I will keep this thread open for few more hours and I will split the points based on the contribution.
Thanks again.
David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all for your suggestions....seems that I have lots of works in the week ahead.
David
David
ASKER
https://www.experts-exchange.com/questions/22775839/Pagination-Problem-Need-your-Suggestion.html
Thanks in advance
David