StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPagination help in SQL
text
Body
copied!<p>The below inner SELECT returns huge amount of rows (1000000+) and the outer SELECTs(alpha BETWEEN #startRec# AND #endRec#) is used for PAGINATION to display data with 25 in each page.</p> <p>Issue is:-This PAGINATION done below is very slow and slows the entire display of data.So could all please help me on doing this below pagination in a BETTER WAY? COde about pagination would be best.</p> <p>**I am very sorry to put in this way but i am very new to Pagination concepts and so need your help.</p> <pre><code>/*********ORIGINAL QUERY ****/ SELECT * FROM ( SELECT beta.*, rownum as alpha FROM ( SELECT p.lastname, p.firstname, porg.DEPARTMENT, porg.org_relationship, porg.enterprise_name, ( SELECT count(*) FROM test_person p, test_contact c1, test_org_person porg WHERE p.p_id = c1.ref_id(+) AND p.p_id = porg.o_p_id $where_clause$ ) AS results FROM test_person p, test_contact c1, test_org_person porg WHERE p.p_id = c1.ref_id(+) AND p.p_id = porg.o_p_id $where_clause$ ORDER BY upper(p.lastname), upper(p.firstname) ) beta ) WHERE alpha BETWEEN #startRec# AND #endRec# </code></pre> <hr> <p>My tried implementation below</p> <hr> <p>(1)The inner most query..is the 1st QUERY fetching the data. (2)Then,we do a total COUNT on the above data.</p> <p>Now,main issue is running the query goes on forever....and finally i have to forcibly cancel it. I feel there is something missing in the below query for which it hangs off. </p> <p>Also,I came to know doing the COUNT outside is the best approach for performance.So,could you please correct the query below so that I am able return the COUNT *** DATA using Pagination,rownum etc.Mainly with the aliases below,rownum and getting data.</p> <pre><code>select * from ( select x.* ,rownum rnum from ( SELECT count(*) as results /****2nd QUERY is OUTSIDE to get total count**/ </code></pre> <p>Question is here,how do i access the data selected inside the 1st query below</p> <pre><code>from ( /****1st query to SELECT data***/ SELECT p.lastname, p.firstname, porg.DEPARTMENT, porg.org_relationship, porg.enterprise_name FROM t_person p, t_contact c1, t_o_person porg WHERE rownum <10 and p.person_id = c1.ref_id(+) AND p.person_id = porg.o_person_id ORDER BY upper(p.lastname), upper(p.firstname) ) y ------------------>alias defined Y from data of the 1st query )x ------------------>alias defined X where rownum <= 20 ) where rnum >= 1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload