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.
Best Answer
Are there other processes operating on the same tables? Indexes being rebuilt?
If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/