OraFAQ Forum: SQL & PL/SQL » Pull 20 records for each time

Home » SQL & PL/SQL » SQL & PL/SQL » Pull 20 records for each time (Oracle Pl/sql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pull 20 records for each time [message #646831]

Mon, 11 January 2016 07:01

itnagaraj
Messages: 3
Registered: January 2016
Location: Chennai

Junior Member

How to pull 20 records for each time?.

I have created the stored procedure to display the records in front end.

But client requirement is to pull 20 records for first time and click the next button, we pull the records from next 20 records(21 to 40) to avoid the performance issue.

Klindly provide the solution for this.

Advance thanks.

Report message to a moderator

Re: Pull 20 records for each time [message #646832 is a reply to message #646831]

Mon, 11 January 2016 07:05

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

orclz>
orclz> select ename from emp order by ename fetch first 4 rows only;

ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK

orclz> select ename from emp order by ename offset 4 rows fetch next 4 rows only;

ENAME
----------
FORD
JAMES
JONES
KING

orclz> select ename from emp order by ename offset 8 rows fetch next 4 rows only;

ENAME
----------
MARTIN
MILLER
SCOTT
SMITH

orclz>

[Updated on: Mon, 11 January 2016 07:05]

Report message to a moderator

Re: Pull 20 records for each time [message #646834 is a reply to message #646832]

Mon, 11 January 2016 07:08

itnagaraj
Messages: 3
Registered: January 2016
Location: Chennai

Junior Member

Thanks for your reply.

I have 1,75,00,000 record in my table.

How to handle in SP.

Report message to a moderator

Re: Pull 20 records for each time [message #646835 is a reply to message #646834]

Mon, 11 January 2016 07:13

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

1. I doubt that your users intend to page through 17500000 rows. They would be clicking Next from now until the end of eternity.

2. Why use PL/SDQL when SQL will do?

3. I wish you wouldn't say "record" when you mean "row".

Report message to a moderator

Re: Pull 20 records for each time [message #646836 is a reply to message #646835]

Mon, 11 January 2016 07:19

itnagaraj
Messages: 3
Registered: January 2016
Location: Chennai

Junior Member

Hi John,

This is my stored procedure.

create or replace PROCEDURE GET_MEMBER_DETAILS (p_siteid IN VARCHAR2 DEFAULT NULL,
p_premiumid IN VARCHAR2 DEFAULT NULL,
p_rewardid IN VARCHAR2 DEFAULT NULL,
p_activeflag IN VARCHAR2 DEFAULT NULL,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN

OPEN p_recordset FOR
SELECT M.MEM_ID,
M.EMAIL,
M.PASSWORD,
M.FNAME,
M.LNAME,
M.JOIN_DATE,
M.SOL_ID,
M.PREMIUM_ID,
M.REWARD_ID,
M.EBATES_MERCHANT_GROUP_ID,
M.SITE_ID,
M.PRODUCT_ID,
M.BILL_STATUS_ID,
M.BILL_CYCLE_START_DATE,
M.AUTOLOGIN_KEY,
DECODE(M.ACTIVE_FL, 1, 'True', 'False') as ACTIVE_FL,
S.SITE_NAME
FROM
Member M, SITE S
WHERE
(p_siteid is null or S.SITE_ID = p_siteid)
AND (p_premiumid is null or M.PREMIUM_ID = p_premiumid)
AND (p_rewardid is null or M.REWARD_ID = p_rewardid)
AND (p_activeflag is null or M.ACTIVE_FL = p_activeflag)
AND M.SITE_ID = S.SITE_ID
ORDER BY MEM_ID;

END;

If i am passing all parameter as null, it will return 1,75,00,000 records.

First time i will pull first 20 records, second time next 20 records etc.

How to implement this?

Report message to a moderator

Re: Pull 20 records for each time [message #646837 is a reply to message #646836]

Mon, 11 January 2016 07:41

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Handle it in the calling program.

Report message to a moderator

Re: Pull 20 records for each time [message #646838 is a reply to message #646837]

Mon, 11 January 2016 07:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Also:

John Watson wrote on Mon, 11 January 2016 14:05

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Report message to a moderator

Re: Pull 20 records for each time [message #646839 is a reply to message #646838]

Mon, 11 January 2016 08:24

garan
Messages: 27
Registered: January 2016

Junior Member

Hi nagaraj

first of all it's not clear from your post in which version of oracle you are working.
The features mentioned above "fetch first 4 rows only" works only in oracle 12C and will not work with Oracle 11g
You might have to use rownum to address these in oracle 10g/11g

To get first 20rows

SELECT * FROM EMP WHERE ROWNUM < 21

To get next 20 rows

SELECT * FROM EMP WHERE ROWNUM < 41 MINUS SELECT * FROM EMP WHERE ROWNUM < 21

The idea of solution is assuming you have 100 rows I will break into 100/20 = 5 sets and keep retrieving in sets one at a time

I feel 1,75,00,000 records is too huge to get 20 records at a time and there will be lot of unnecessary database rounds trips instead I would prefer retrieving 20000 records at one shot to the frontend and cache it and have a pagination logic to get 20 records at a time from the cache.

regards
garan

Report message to a moderator

Re: Pull 20 records for each time [message #646840 is a reply to message #646839]

Mon, 11 January 2016 08:46

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you're using a ref cursor the correct solution is to get the calling program to fetch only 20 rows at a time as Michel already said.
Ref cursors will only fetch as many rows in a go as you tell them to.

Also this won't work:
garan wrote on Mon, 11 January 2016 14:24

To get next 20 rows

SELECT * FROM EMP WHERE ROWNUM < 41 MINUS SELECT * FROM EMP WHERE ROWNUM < 21

There is no guarantee that oracle will assign rownum to the rows in the same order in those to queries - it probably will but you can't rely on it.
Have a look on AskTom for the correct way to do pagination queries.

Report message to a moderator

Previous Topic:	Generate unique combinations
Next Topic:	Not able to insert with returning option on view with instead of trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 13:59:34 CDT 2024