Index Rebuild

From time to time I see people asking about Oracle error ORA-01410: invalid rowid. It’s a problem I’ve responded to a few times in newsgroups and presentations, but I realised recently that despite mentioning it casually for several years I’ve never written a specific note about it – so here it is.

There are probably several reasons why you might see Oracle error ORA-01410, including the obvious one that you’ve created a rowid in the front-end code that cannot correspond to a real rowid. However, a much more subtle, yet relatively frequent, cause of the error comes from the habit of using automatic programs to rebuild multiple indexes.

Although most indexes in Oracle can safely be left to keep themselves tidy and efficient some DBAs (possibly many DBAs) have been persuaded that rebuilding indexes regularly is a good thing. Other DBAs are a little more cautious and write programs that attempt to make a sensible decision about which indexes really do need to be rebuilt – and then report suspect indexes rather than rebuilding them.

Even if you fall into the latter group and do manage to identify the few indexes that can benefit from being rebuilt, be careful about letting the rebuilds take place automatically – the timing can be critical.

Consider this: if you rebuild an index (whether or not you use the online option) Oracle will copy the index contents from one location to another and, when the copy is complete, free up the space that held the original index contents.

What’s going to happen to a long running query that started running before you did your rebuild ? Surprisingly the query will carry on running using the “old” index. Oracle introduced a form of ‘cross-DDL read consistency’ many years ago to make partition maintenance operations possible, and that’s the feature that allows your query to carry on running.

But what’s going to happen if some other process now demands the space and puts something into it – for example by creating or extending a table or index. The next time your long-running query hits the “old” index it may find itself looking at a block that contains the wrong type of data for the wrong object id. And so you get Oracle error ORA-01410: invalid rowid.

The effect is easy to demonstrate if you start with a completely empty tablespace (preferably locally managed with uniform extents, though this isn’t an absolute necessity):

create table t1 as
select
	rownum	id,
	rpad(rownum,10)	v1,
	rpad('x',100)	v2
from
	all_objects
where
	rownum <= 10000
;

alter table t1 add constraint t1_pk primary key(id);

execute dbms_stats.gather_table_stats(user,'t1')

declare
	m_v1 varchar2(10);
begin
	for r in (select /*+ index(t1) */ v1 from t1 where id > 0)
	loop
		m_v1 := r.v1;
		dbms_lock.sleep(0.01);

	end loop;
end;
/

All I do is create an indexed table, then run a simple procedure that is forced to walk through the index very slowly.

Now start up a new session for the same user (and you have about 100 seconds to do this)  and execute the following statement:

alter index t1_pk rebuild online;
create table t2 as select * from t1;

The new table t2 should fill the hole left by rebuilding the index t1_pk, and your first session should crash moments later.

The behaviour is much less predictable on production systems, of course. In the real world a random process may simply crash at some random time in the overnight batch once every few weeks for no apparent reason.

Footnote: there is an alternative to ORA-01410 that can appear if you rebuild indexes while running large reporting jobs. If, for example,  your code executes “set transaction read only “ and then runs lots of separate SQL statements, a statement that needs an index that was rebuilt after the “set transaction” call will fail with “ORA-08176: consistent read failure; rollback data not available”.

Update (Jan 2013):

I noticed a relatively recent incoming referral on this item that shows the problem is still happening in 2012 – you might want to read it as an example of how the symptoms can appear.

 

[Further reading on rebuilding indexes]