Oracle SQL-Pagination Help
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#
Software/Hardware used:
Software/Hardware used:
ASKED:
February 16, 2009 4:24 PM
UPDATED:
January 2, 2010 6:20 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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> “;
}
?>