ora-01410 invalid RowID sporadically in a select statement

Question

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.

Answer 1

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/