Home » SQL & PL/SQL » SQL & PL/SQL » Select set of records from a table
Select set of records from a table [message #196035] Tue, 03 October 2006 15:06 Go to next message
desigan
Messages: 8
Registered: October 2006
Junior Member
Hi,

There are around 10000 records in a table EMPLOYEE, where key column is EMPID.

For every record in the table, we have to do a lenghty process. Hence, we have decided to run the process in set of 2000 records parallelly. Example: Take first 2000 records and start processing them. Parallely take next 2000 records and start processing them and so on.

What is the best way of achieving it? [One way is to have EMPID and another running sequence number in a temp table. Then process records based on this running sequence number.]

Thanks and Regards,
Desigan
Re: Select set of records from a table [message #196086 is a reply to message #196035] Wed, 04 October 2006 01:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd probably have one driving process that split the records up into 5 groups based on empid, and then fired off 5 dbms_job or Dbms_scheduler processes, one to deal with each group.
Re: Select set of records from a table [message #197021 is a reply to message #196086] Mon, 09 October 2006 10:34 Go to previous messageGo to next message
desigan
Messages: 8
Registered: October 2006
Junior Member
JRowbottom wrote on Wed, 04 October 2006 01:48

I'd probably have one driving process that split the records up into 5 groups based on empid, and then fired off 5 dbms_job or Dbms_scheduler processes, one to deal with each group.


Thanks for the reply JRowbottom.

My doubt was about the best way of writing that driving process. Following are few of the ways, that came to my mind.

1) Using rownum to split records - But, not sure if it is best to rely on this rownum.

2) Have a temp table that will have two columns - One column will have empid and other column will be running sequence number. I will take records based on this sequence number from one to 2000 as one set, 2001 - 4000 as second set and so on...
But the drawback here is that a new temp table should be created.

3) Take all records from the table ordered by empid. Then take 1, 2001, 4001 and so on.. EMPID from table. Then write a query to take records from the table such that (EMPID <= (2001_EMPID)).

Is there any better way of doing it? Any ideas?

Regards,
Desigan
Re: Select set of records from a table [message #197105 is a reply to message #197021] Tue, 10 October 2006 01:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use the NTILE analytic function to get the start and end Empids of the 5 groups:
SQL> create table emp_test (empid  number, ename  varchar2(30));

Table created.

SQL> insert into emp_test (select level, 'Employee '||level from dual connect by level <= 132);

132 rows created.

SQL> select grp,min(empid), max(empid)
  2  from  (select empid,ntile(5) over (order by empid) grp
  3         from   emp_test)
  4  GROUP BY GRP;

       GRP MIN(EMPID) MAX(EMPID)
---------- ---------- ----------
         1          1         27
         2         28         54
         3         55         80
         4         81        106
         5        107        132

If you run the query
select empid,ntile(5) over (order by empid) grp
from   emp_test
by itself, you'll see what the NTILE function does - basically it splits the set of rows into 5 group with no more than 1 row difference in size between them.

Hope this helps.
Re: Select set of records from a table [message #197483 is a reply to message #197105] Wed, 11 October 2006 07:41 Go to previous message
desigan
Messages: 8
Registered: October 2006
Junior Member
Thanks JRowBottom...

Thats exactly what I wanted and NTILE would help my code to be clean...

Thanks for the reply...

Regards,
Desigan
Previous Topic: see my problem in query
Next Topic: passing an array as argument to a function
Goto Forum:
  


Current Time: Mon Dec 05 23:54:27 CST 2016

Total time taken to generate the page: 0.08435 seconds