Oracle SQL-Pagination Help

25 pts.
Tags:
Oracle SQL
Oracle SQL Pagination
Pagination
SELECT statement
Hi, 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. 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. **I am very sorry to put in this way but i am very new to Pagination concepts and so need your help 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#
ASKED: February 16, 2009  4:24 PM
UPDATED: January 2, 2010  6:20 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

A lot depends on how you are using the results. If this is a frequent query, you might consider creating a Materialized View on the data, including the rownumber as a column in the view.
With a Materialized View, you can index the columns and run very efficient queries against the view, so your pagination on the rownumber range will be very fast.
However, the tradeoff is how frequently the underlying tables are updated versus the queries, and how “current” the results need to be.
If the tables are relatively static (infrequent updates – say once a day or less), then the view could be real-time. If the tables are updated a lot throughout the day, then you might want to schedule updates to the view, instead of rebuilding the view every time an underlying row changes. If the result data didn’t need to be absolutely current, you could schedule the view to refresh once a day, for example.

Materialized Views are very simple in Oracle, and very powerful for creating indexable joined data structures. See “Create Materialized View” in your Oracle documentation.

Note – because the entire rownumber set is likely to be recomputed with each change to the underlying tables, you should probably configure this view to do a complete refresh instead of trying to do a “fast” (or incremental) refresh. A complete refresh is essentially a drop and recreate of the view, which is probably going to be faster than trying to recompute and adjust every row on a name change.

Of course, if the tables are frequently updated, and this query is run infrequently and needs to have the most current information, then your existing query may be the best solution.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Adifakhzani
    Here sample code, pagination for Oracle (taken from php-mysql code sample). Hope it's help <? //GET SEARCH FILTER VALUE - OPTIONAL if($_REQUEST['MercType']!="") { $qMercType" and merc_type='".$_REQUEST['MercType']."' "; } //GET TOTAL ROWS $querylist_total="SELECT count(merc_id) AS totalrow FROM merc WHERE status='1' $qMercType ORDER BY merc_id DESC"; $resultlist_total=odbc_exec($connect,$querylist_total); odbc_fetch_row($resultlist_total); $totalrow=odbc_result($resultlist_total,'totalrow'); ?> <? // LIST ITEM // define cuurent page value if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { $currentpage = (int) $_GET['currentpage']; } else { $currentpage = 1; } $rowsperpage = 20; $startrow = (($currentpage -1) * $rowsperpage) +1; $endrow = $startrow + $rowsperpage - 1; $range = 10; //list of wr by this staff $querylist="SELECT merc_id, mecr_name, description, total_rows FROM ( SELECT merc_id, mecr_name, description, total_rows, rownum row_counter FROM ( SELECT merc_id, mecr_name, description, count(*) OVER () total_rows FROM merc WHERE status='1' $qMercType ORDER BY merc DESC ) ) WHERE row_counter between $startrow and $endrow "; $resultlist=odbc_exec($connect,$querylist); while(odbc_fetch_row($resultlist)) { // list merc echo odbc_result($resultlistwr,'merc_id'); echo odbc_result($resultlistwr,'mecr_name'); echo odbc_result($resultlistwr,'description'); echo "<br>"; } ?> <? // PAGINATION at bottom of page if ($currentpage > 1) { echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; $prevpage = $currentpage - 1; echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; } for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { $y = ($x-1) * $rowsperpage; if (($x > 0 ) && ( $y <=$totalrow )) { if ($x == $currentpage) { echo " [<b>$x</b>] "; } else { echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; } } } $y = $currentpage * $rowsperpage; $lastpage = ceil($totalrow / $rowsperpage); if( $y <=$totalrow ){ $nextpage = $currentpage + 1; echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$lastpage'>>></a> "; } ?>
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following