The results of cursor queries that perform joins may produce varying results, depending on the join order chosen for the query, if updates to the join column take place during the session. The following two examples illustrate how join order can affect cursor results sets.
select * from dept
dept_id deptloc
------- -----------------
1 Elm Street
2 Acacia Drive
3 Maple Lane
4 Oak Avenue
select * from employee
dept_id empid
------- ---------
1 172321176
1 213468915
2 341221782
2 409567008
2 427172319
3 472272349
3 486291786
These statements declare a cursor, open it, and fetch the first row:
declare j_curs cursor for select d.dept_id, e.empid, d.deptloc from dept d , employee e where d.dept_id = e.dept_id and d.dept_id = 2
open j_curs
fetch j_curs
dept_id empid deptloc
----------- ----------- --------------------
2 341221782 Acacia Drive
If dept is chosen as the outer column in the join order, the value 2 for dept_id is buffered. The following update changes the dept_id of the join column in dept:
update dept set dept_id = 12 where current of j_curs
This update changes the value stored in the table row, but does not alter the buffered value, hiding the result of this update to the base table from the cursor. The effect would be the same if the join column in dept were updated by a non-positioned update.
This update changes the dept_id of the employee row:
update employee set dept_id = 12 where current of j_curs
The next fetch on the table returns the second row in the result set:
fetch j_curs
dept_id empid deptloc
----------- ----------- --------------------
2 409567008 Acacia Drive
All matching rows from employee can be fetched and updated.
If the join order for this cursor selects employee as the outer table, and the join column in dept is updated after the first fetch, the second fetch finds no matching rows. This is the sequence of steps:
The value 2 for employee.dept_id is buffered during the first fetch.
A searched or positioned update changes the value of dept.dept_id to 12.
At the second fetch, 2, the buffered value for employee.dept_id is used, and the inner table is scanned for matching values; since dept.dept_id has been changed to 12, the second fetch does not return a row.
The two remaining rows in employee with dept_id equal to 2 cannot be fetched by the cursor.
Due to buffering of search arguments, the sensitivity of cursor results to updates of the search arguments on cursor select queries also depends on join order. This example uses the dept and employee tables as shown in “Effects of column buffering during cursor scans”. The following cursor joins on the dept_id columns of the table, using a search argument on the deptloc column:
declare c cursor for select d.dept_id, empid, deptloc from dept d , employee e where d.dept_id = e.dept_id and deptloc = "Acacia Drive"
The first fetch on this table returns this row:
dept_id empid deptloc
--------- ----------- --------------
2 41221782 Acacia Drive
This positioned update statement changes employee.dept_id value to 4 for the current row; it needs to be performed for each employee in the department located at Acacia Drive:
update employee set dept_id = 4 where current of c
This positioned update changes the dept.deptloc value from “Acacia Drive” to “Pine Way”,
update dept set deptloc = "Pine Way" where current of c
If the update to deptloc is issued after the first fetch, the result set may or may not return all of the rows that need to be changed; the results depend on the join order chosen for the query:
If dept is chosen as the outer table, the dept.dept_id and dept.deptloc columns, the values “2” and “Acacia Drive”, are buffered. After the first fetch, the update to deptloc updates the base table, but not the buffered values, and subsequent fetch commands return additional result set rows.
If employee is chosen as the outer table, only the dept_id qualification on the employee table is buffered. The update to the deptloc column changes the base table, and when the search argument qualification “Acacia Drive” is applied to the deptloc table at the next fetch, no rows are returned, even though two rows with employee.dept_id of 2 remain in the table.
Note that issuing the update to deptloc after all rows from the employee table have been fetched would accomplish both updates, without a dependency on the join order.
Columns from the select list of the outer table in the join order are buffered when a row from the outer table is fetched. If a searched or positioned update is performed on a column in the select list, and another row is fetched from the inner table, the buffered value from the outer table appears in the cursor result row.
