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 (เช่นสร้าง table ใน tablespace เดียวกัน). 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):
###Prepare Environment
create table t1 nologging 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');
###Run Some SQL Report on Session1
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;
/
###Open Session2 And Doing Following Command
alter index t1_pk rebuild online nologging;
create table t2 nologging 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
Credit : Jonathan Lewis
Hope this help you to more understand :D
Chatchai Komrangded
