Oracle 10g Enterprise Edition Release 10.2.0.4.0 running on Windows Server 2003
Oracle Client 10.2.0.2.0 running on Windows 2000
I have some PL/SQL code that's intended to update a column in a table based on a lookup from another table. I started out by testing it with the UPDATE statement commented out, just visually inspecting the DBMS_OUTPUT results to see if it was sane. During this testing I added/changed the cursor ORDER BY clause to make it easier to read the output, and saw some strange results. I've run the code 3 times with:
1. no ORDER BY clause
2. ORDER BY with two columns (neither indexed)
3. ORDER BY with one column (not indexed)
and get three different "rows updated" counts - in fact, when using the ORDER BY clauses it appears that the code is processing
more rows than without either ORDER BY clause. I'm wondering why adding / changing an ORDER BY <non-indexed column> clause in a cursor would affect the row count?
The code structure is:
-----
TYPE my_Table_t IS TABLE OF table1%ROWTYPE ;
my_Table my_Table_t ;
CURSOR my_Cursor IS SELECT * FROM table1 ; -- initial case - no ORDER BY clause
-- ORDER BY table1.column1, table1.column2 ; -- neither column indexed
-- ORDER BY table1.column2 ; -- column not indexed
my_Loop_Count NUMBER := 0 ;
OPEN my_Cursor ;
LOOP
FETCH my_Cursor BULK COLLECT INTO my_Table LIMIT 100 ;
EXIT WHEN my_Table.COUNT = 0 ;
FOR i IN 1..my_Table.COUNT LOOP
my_New_Value := <call a pkg.funct to retrieve expected value from another table> ;
EXIT WHEN my_New_Value IS NULL ;
EXIT WHEN my_New_Value = <an undesirable value> ;
IF my_New_Value <> my_Table(i).column3 THEN
DBMS_OUTPUT.PUT_LINE( 'Changing ' || my_Table(i).column3 || ' to ' || my_New_Value ) ;
/*****
UPDATE table1 SET column3 = my_New_Value WHERE column_pk = my_Table(i).column_pk ;
*****/
my_Loop_Count := my_Loop_Count + 1 ;
END IF ;
END LOOP ;
COMMIT ;
END LOOP ;
CLOSE my_Cursor ;
DBMS_OUTPUT.PUT_LINE( 'Processed ' || my_Loop_Count || ' Rows ' ) ;