I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this
SELECT distinct t1.crit FROM table_name1 t1 INNER JOIN table_name2 t2 ON t1.crit = t2.crit WHERE NVL (t2.qty, 0) + NVL (t2.adds, 0) - NVL (t2.remove, 0) > 0 AND t1.process_id = 'some_process_id' AND t1.item_no = 'some item NO' AND t1.to_gen = 1
where t1 has an index on (item_no, crit), and t2 has an index on (item_no, crit, X), and X is some additional column.
The objective of this statement is to find all candidates for which I need to generate data in additional tables. When the new data has been generated, the column t1.to_gen is set to 0. At the moment it is not clear, if the software which runs this statement, does intermediate commits, e.g. generates some new data for one item_no, sets t1.to_gen = 0 and commits, before using the next item_no from the cursor of the statement in question.
This statement is executed successfully several times a day in my environment, but in rare cases I get an error from oracle, whose source is not clear to me:
ora-01410: invalid RowID
As the select statement does not use the pseudo-column RowID direcly, Oracle will have to use it internally.
What is the source of this problem?
Update: Sad as it sounds, the system does intermediate commits, but that is not the source of the problem.