Analytics row_number to identify first parent row in oracle pl/sql cursor loop
Let’s say you have 10m parent rows and 11m child rows and need to process them row-at-a-time in pl/sql. Two methods of doing that are below.
- The second method will fire the combined select just the once, picking up all 11m rows in one go.
- The first method will fire the child select 10m times individually, with a different id value passed into the where clause each time. It uses bind variables for that, so doesn’t hard parse 10m times, but still will take time.
The first method is the simplest to read, maintain and debug, so stick with that unless performance is required.
Method 1 – traditional way, two cursor loops
01 | BEGIN |
02 | FOR r_dept IN ( SELECT deptno , dname FROM dept ) |
03 | LOOP |
04 | -- put parent (department) row processing code here. |
05 | FOR r_emp IN ( SELECT empno , ename FROM emp WHERE emp.deptno = r_dept.deptno ) |
06 | LOOP |
07 | -- put child (employee) row processing code here. |
08 | END LOOP ; |
09 | END LOOP ; |
10 | END ; |
Method 2 – single cursor loop
01 | BEGIN |
02 | FOR r_both IN ( |
03 | SELECT dept.deptno |
04 | , dept.dname |
05 | , emp.empno |
06 | , emp.ename |
07 | , ROW_NUMBER() OVER ( PARTITION BY dept.deptno ORDER BY dept.deptno , emp.empno ) AS rn |
08 | FROM dept |
09 | LEFT OUTER JOIN emp -- outer join if you want to process childless parents = departments with no employees assigned. |
10 | ON dept.deptno = emp.deptpno |
11 | ) |
12 | LOOP |
13 | IF r_both.rn = 1 -- starting to work on a different parent (department) record. |
14 | THEN |
15 | -- put parent (department) row processing code here. |
16 | END IF ; |
17 | -- put child (employee) row processing code here. |
18 | END LOOP ; |
19 | END ; |
Leave a Reply