rohit d
2009-02-16 08:55:25 UTC
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#