Oracle: Unusable Indexes

March 25, 2008

by JP Vijaykumar

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/

http://www.dba-oracle.com/t_indexes_invalid_unusable.htm

http://www.oracledba.co.uk/tips/truncate_unusable.htm

http://www.adp-gmbh.ch/ora/concepts/rowid.html

http://thinkoracle.blogspot.com/2007_05_01_archive.html

http://zengin.wordpress.com/2007/07/19/rowid-format/

http://www.psoug.org/reference/pseudocols.html

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=31587

http://www.unix.com.ua/orelly/oracle/bipack/ch09_01.htm#ch09-21687

http://www.directions.com.au/articlehtmlxid_149








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers