Oracle: Unusable Indexes

March 25, 2008

It is beyond the scope of
this document to present all the scenarios that mark an index as unusable and how
to validate unusable indexes. Instead, we are going to see what happens to an
index when it becomes unusable.

Before proceeding further,
let‘s discuss what an index is. An index on a table is an object, that stores
the table’s column values in a sorted order (by default ascending order) along with
the corresponding rowids.

The column sort order can be
specified in the index creation script as:

create index temp_jp_idx on temp_jp(col1 asc);
 create index temp_jp_idx on temp_jp(col1 desc);

What is a rowid? A rowid is
a pseudo column that uniquely identifies a row within a table. A rowid
represents the physical location of the corresponding row in the table.

The format of a rowid is <object
id><file id><block id><row number>, and is represented as
OOOOOOFFFBBBBBBRRR (numbers 6,3,6,3). This is the Extended rowid format from 9i
onwards.

If a table is altered through
a move command, why do the indexes on a table become unusable?

If the physical location of
the table’s data changes during an operation, then Oracle marks the indexes on
the table as unusable.

The current rowids of the
table’s data, after the move, point to new location. The old rowids in the
index, from before the move command, still point to the old location where
there is no data. Oracle cannot access data using an index with invalid rowids.
As such Oracle marks all the indexes on a table as unusable after the move
command.

To analyze the issue, I
simulated a test case.

drop table temp_jp;
drop table temp_rowids_jp;
create table temp_jp(col1 number,col2 varchar2(20)) tablespace users1;
insert into temp_jp values(1,'RAMA');
insert into temp_jp values(3,'SITA');
insert into temp_jp values(2,'LAKSHMAN');
commit;
set autot on exp
select * from temp_jp where col1=3;
      COL1 COL2
---------- --------------------
         3 SITA
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=25)
   1    0   TABLE ACCESS (FULL) OF 'TEMP_JP' (TABLE) (Cost=3 Card=1 Bytes=25)

Oracle performed a full table
scan on temp_jp table, as there is no index on the table.

Created an index on temp_jp
table.

create index temp_jp_idx on temp_jp(col1 asc);
set autot on exp
select * from temp_jp where col1=3;
      COL1 COL2
---------- --------------------
         3 SITA
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=25)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEMP_JP' (TABLE) (Cost=2  Card=1 Bytes=25)
   2    1     INDEX (RANGE SCAN) OF 'TEMP_JP_IDX' (INDEX) (Cost=1 Card  =1)

Now Oracle accessed temp_jp
table’s data using the temp_jp_idx index.

select index_name, status  from dba_indexes where table_name='TEMP_JP';
INDEX_NAME                     STATUS
------------------------------ --------
TEMP_JP_IDX                    VALID

This temp_jp_idx is valid and
Oracle is using this index while accessing temp_jp table’s data.

Find the current rowids of
temp_jp table’s data.

select rowid,a.* from temp_jp a;
ROWID                    COL1 COL2
------------------ ---------- --------------------
AAA16tAAEAAAADoAAA          1 RAMA
AAA16tAAEAAAADoAAB          3 SITA
AAA16tAAEAAAADoAAC          2 LAKSHMAN

I will preserve the current rowids
from temp_jp table before the move for future reference.

create table temp_rowids_jp as select col1,rowid as before_move_rowid from temp_jp;

Find the object id, file id,
block id and row number from the rowid:

select dbms_rowid.rowid_object(rowid) "object",
      dbms_rowid.rowid_relative_fno(rowid) "file",
      dbms_rowid.rowid_block_number(rowid) "block",
      dbms_rowid.rowid_row_number(rowid) "row" from temp_jp
    /
    object       file      block        row
---------- ---------- ---------- ----------
    220845          4        232          0
    220845          4        232          1
    220845          4        232          2

Let’s find the object owner,
object name and object type from dba_object for the object id 220845.

select object_id,owner, object_name, object_type,created from dba_objects where object_id = 220845;
OBJECT_ID   OWNER  OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ ---------------  ------------------- ---------
   220845   SCOTT  TEMP_JP          TABLE             28-FEB-08
select object_id,owner,object_name,object_type,created 
   from dba_objects  where created > trunc(sysdate) -1/24;
OBJECT_ID   OWNER  OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ ---------------  ------------------- ---------
   220846   SCOTT  TEMP_JP_IDX       INDEX              28-FEB-08
   220845   SCOTT  TEMP_JP           TABLE              28-FEB-08
   220847   SCOTT  TEMP_ROWIDS_JP    TABLE              28-FEB-08
select object_id,owner,object_name,object_type,created 
from dba_objects   where object_id in (select max(object_id) from dba_objects);
OBJECT_ID   OWNER  OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ ---------------  ------------------- ---------
   220847   SCOTT  TEMP_ROWIDS_JP    TABLE              28-FEB-08

Let’s query the temp_jp
table’s data using the current rowid.

select * from temp_jp where rowid = 'AAA16tAAEAAAADoAAB';
      COL1 COL2
---------- --------------------
         3 SITA

The rowids are correctly
pointing to the location of the temp_jp table’s rows. The index temp_jp_idx on
emp_jp table has the right rowids for the table’s data. The object_id from the
rowid is converting to the right table. We can access the table’s data using
the rowids.

Now let us move the table to
a different tablespace and see.

alter table temp_jp move tablespace users2;
SQL> select * from temp_jp where rowid = 'AAA16tAAEAAAADoAAB';
select * from temp_jp where rowid = 'AAA16tAAEAAAADoAAB'
              *
ERROR at line 1:
ORA-01410: invalid ROWID

After the table move, the rowids
changed. The old rowids no longer point to the actual table’s data. My select
query on temp_jp table using rowid worked before the move command and failed
after the move command.

Let’s find the current rowid
for the temp_jp table’s data, after the table is altered with a move command.

select rowid,a.* from temp_jp a;
ROWID                       COL1        COL2
------------------          ---------- --------------------
AAA16wAAMAAAC+MAAA          1           RAMA
AAA16wAAMAAAC+MAAB          3           SITA
AAA16wAAMAAAC+MAAC          2           LAKSHMAN

The rowids in temp_jp table
changed after the move command. From the definition of rowid, we observe that
rowid consists of
<object_id><file_id><block_id><row_num>. Since the
table moved from users1 tablespace to users2 tablespace, the physical
attributes of the rows, like file_id and block_id changed. Hence the rowids in
temp_jp table changed.

Let’s find the object_id,
file_id, block_id and rownumber from the new rowids of the table temp_jp after
the move command:

select dbms_rowid.rowid_object(rowid) "object",
          dbms_rowid.rowid_relative_fno(rowid) "file",
          dbms_rowid.rowid_block_number(rowid) "block",
          dbms_rowid.rowid_row_number(rowid) "row" from temp_jp
 /
 object       file      block        row
 --------- ---------- ---------- ----------
 220848         12      12172          0
 220848         12      12172          1
 220848         12      12172          2

After the move command,
Oracle assigned a new object_id to the table. The file number that belongs to
the users2 tablespace is 12. The current block number is 12172. The rownumbers
remained the same. The move command has moved the table’s location, did not
change the rows order inside the table.

Let’s find the object details
using the new object_id:

select object_id,owner, object_name, object_type,created from dba_objects where object_id = 220848;
no rows selected

The objectid from the new rowid
after the move command is not retrieving the object’s details. Instead the
object id for the table temp_jp from dba_objects view is displaying the old
object_id from before the move command.

select object_id,owner, object_name, object_type,created from dba_objects
where object_name = 'TEMP_JP';
OBJECT_ID   OWNER  OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ --------------- -------------------  ---------
220845      SCOTT  TEMP_JP          TABLE               28-FEB-08

This might be a bug in
Oracle.

I want to find the objects
that were created in the past one hour:

select object_id,owner,object_name,object_type,created 
from dba_objects  where created > trunc(sysdate) -1/24;
OBJECT_ID OWNER  OBJECT_NAME     OBJECT_TYPE           CREATED
--------- ------ --------------- -------------------   ---------
220846    SCOTT  TEMP_JP_IDX      INDEX                28-FEB-08
220845    SCOTT  TEMP_JP          TABLE                28-FEB-08
220847    SCOTT  TEMP_ROWIDS_JP   TABLE                28-FEB-08
select object_id, owner, object_name, object_type, created 
from dba_objects where object_id in (select max(object_id) from dba_objects);
OBJECT_ID OWNER   OBJECT_NAME     OBJECT_TYPE          CREATED
--------- ------- -------------   -------------------  ---------
220847    SCOTT   TEMP_ROWIDS_JP  TABLE                28-FEB-08

Let us query the table
temp_jp using the new rowid:

select * from temp_jp where rowid = 'AAA16wAAMAAAC+MAAB';
      COL1 COL2
---------- --------------------
         3 SITA

We could query data from the
table using the new rowid. However, the objectid derived from the new rowid is
not retrieving the object details. From this, we observe that Oracle is not
using the objectid from the current rowid to retrieve the table’s data. Oracle
used the fileid, blockid and row number to access the data.

Let’s see the status of our
index on temp_jp table after the move command.



select index_name, status from dba_indexes where table_name=’TEMP_JP’;

INDEX_NAME                     STATUS

—————————— ——–

TEMP_JP_IDX                    UNUSABLE

 set autot on exp

 select * from temp_jp where col1=3;

      COL1    COL2

———-    ——————–

         3    SITA

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=25)

   1    0   TABLE ACCESS (FULL) OF ‘TEMP_JP’ (TABLE) (Cost=2 Card=1 Bytes=25)

As the existing index on
temp_jp table is unusable, a full table scan was performed on the table.

As you remember, the table temp_jp’s
index still contains invalid rowids. Now let’s see whether we can select data
using the old rowids of the table temp_jp.

SQL> select * from temp_jp where rowid = 'AAA16tAAEAAAADoAAB';
select * from temp_jp where rowid = 'AAA16tAAEAAAADoAAB'
              *
ERROR at line 1:
ORA-01410: invalid ROWID

The old rowids are no longer
pointing to the correct location of the table’s rows. Our index temp_jp_idx
still has the old rowids, which are invalid, from before the move command. As such,
Oracle marked the index as unusable. Nevertheless, we can access the table’s
data using the new rowids

select * from temp_jp where rowid = 'AAA16wAAMAAAC+MAAB';
      COL1 COL2
---------- --------------------
         3   SITA

The index temp_jp_idx should
be re-created with correct rowids, to make it valid.

Let’s compare the rowids of
the table temp_jp before and after the move command:



select a.col1, a.before_move_rowid,b.rowid after_move_rowid

 from temp_rowids_jp a, temp_jp b where a.col1 = b.col1;

     COL1  BEFORE_MOVE_ROWID          AFTER_MOVE_ROWID

———   ——————         ——————

        1   AAA16tAAEAAAADoAAA         AAA16wAAMAAAC+MAAA

        3   AAA16tAAEAAAADoAAB         AAA16wAAMAAAC+MAAB

        2   AAA16tAAEAAAADoAAC         AAA16wAAMAAAC+MAAC

As you might have noticed,
after the move command, the table is moved to a different tablespace, and the objectid,
fileid and blockid changed. Since the data within the table did not change
during the move command, the row numbers remain the same.

During the move command, the
table temp_jp is recreated into users2 tablespace from the earlier tablespace users1.

We had noticed the same from
the file number 12 and block number 12172. As such, there is no data in the old
location at file number 4 and block number 232.

Our index temp_jp_idx, still
contains the old rowids from before the move command, which are not pointing to the correct
location of table’s data. Hence, Oracle marks the index as unusable.

To repair the unusable index,
rebuild the index.



alter index temp_jp_idx rebuild online;

select index_name, status from dba_indexes where table_name=’TEMP_JP’;

INDEX_NAME                      STATUS

——————————  ——–

TEMP_JP_IDX                     VALID

After rebuilding the unusable
index, the index is validated.

set autot on exp
select * from temp_jp where col1=3;
      COL1       COL2
      ---------- --------------------
               3 SITA
      
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=25)
   1    0 TABLE ACCESS (BY INDEX ROWID) OF 'TEMP_JP' (TABLE) (Cost=2   Card=1 Bytes=25)
   2    1 INDEX (RANGE SCAN) OF 'TEMP_JP_IDX' (INDEX) (Cost=1 Card  =1)

After rebuilding the index,
my select queries on the table temp_jp are using the index.

Remember, when you move a
table, the index on the table becomes unusable, but when an index is moved to a
different physical location with a rebuild command, the index never becomes
unusable, why?

This test case was generated
on a Oracle 10.2.0.3.0 database.

Reference

Metalink Note:1057891.6 How
to use dbms_rowid procedure and functions

Metalink Note:1054736.6 How
indexes become unusable?

Metalink Note:165917.1
Common maintenance commands that make index unusable

Metalink Note:222769.1
Description and default values of SQL*Loader parameters

Metalink Note:281500.1
SKIP_UNUSABLE_INDEXES initialization Parameter in Oracle Database 10g

Metalink Note:72512.1
Initialization Parameters

Metalink Note:1054736.6 How
do indexes become index unusable?

Metalink Note:399410.1 Rowid
Structure and Base-64 Conversion

Metalink Note:139707.1
"Update Global Indexes" Allows Automatic Global Index Maintenance
During DDL

http://www.mydigitallife.info/2006/01/28/ora-01502-oracle-index-in-unusable-state/