I modified the below query posted by shiva in the following way, is this query correct??. I tested as well and it is working fine. I cannot understand why shiva has included another inline query in his subquery, can anyone xplain for me please please.
(select messageID, rownum as newcnt
where rownum <= 3)
where newcnt > 0
Please re-read my explanations in this thread and the other referenced threads to fully understand the solution.
Your solution won't work if the result set needs to be ordered, and windowing generally only makes sense with an ordered result set. However, if ordering is not necessary, then your solution will work. I must admit I find it a little humorous that your complaint has to do with what you consider an extraneous layer to the original query while your example truly has one - checking for rownum>0 is definitely extraneous . Don't worry, I know where you were going, just found it funny.
Also, Shiva never actually posted a query, so I'm not actually sure which query you were referring to, not that it really matters though.
Originally posted by ssinha SOmoene suggested that inline views are very slow and degrades performance. Is this true ?
As for the optimal solution, I would still propose the ROWNUM solution. However, it would depend upon certain variables.
The reason the ROWNUM solution works so well is that the optimizer utilizes a STOPKEY method, which simply means that it will stop sorting once it reaches the upper limit (in your case, 600). So, for a single page, a lot of sorting has been avoided. However, if your user is going to end up paging through *the entire* resultset, then we will have actually performed more querying and sorting than if we only pulled the entire resultset once. In other words, if you simply selected all 100,000 rows into a local buffer and scrolled the user through that buffer, you would only have run the query once and sorted the resultset once. However, it is generally true that in *most* searches, where windowing is usually used, very few users will page through more than 5 pages. Most will only page once, maybe twice. Therefore, only sorting and returning the bare minimum number of rows is a better solution. If the user will always use a majority of the resultset, then a single mass query is preferred.
However, one must ask oneself how much use 100,000 rows of information will be to *anyone* on-line. Such massive queries are best left to nightly batch reporting, not on-line queries that the user scrolls through (windowing).
I have more questions hopfully someone can help
Yes most users dont scroll through all 100000 records. But still the requirement is to show for example rows 100 -200 and also display the total count at the bottom off the page which is 100000.
TO get the total count I have to do a count(*) query
I also would like note that the above query has atleast 10 tables and many of which is outer join and hierarchial.,
Does outerjoin cause full table scans or is there anyway to tune all the above combined ?
The first thing to try is to get the requirement removed. It is costly to get that count. However, if you definitely need it, then there are a few options:
- Optimize the query that gets the count. Most queries contain joins to lookup tables and the like to translate codes to user-readable text, etc. Such joins might be able to be eliminated it the COUNT(*) query
- Only run the count(*) query on the first page - don't run it for every page
- If the COUNT query takes a long time anyway, it might be worth re-visiting the '1 big query' approach. Run the entire query and cache all the results. I wouldn't do this blindly - test the various options and know how many pages are usually viewed by the user.