Discussion :

[jokos2000]

Bonjour,

Oracle 9i, AIX 5

Après un move d'une table, lorsque je regarde dans les vues user_indexes et user_objects, je n'ai pas les même résultats :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
select index_name, tablespace_name, status from user_indexes where index_name='CUST_INDEX'
 
INDEX_NAME,TABLESPACE_NAME,STATUS
--------------- ----------------------- ---------
CUST_INDEX,TOOLS,UNUSABLE
 
select object_name, object_type, status from user_objects where object_name='CUST_INDEX'
 
OBJECT_NAME,OBJECT_TYPE,STATUS
----------------- ---------------- ---------
CUST_INDEX,INDEX,VALID

L'objet 'index' semble valide alors que l'index est unusable

Ais-je fait une boulette où est-ce quelque chose de connu ou est-ce bien anormal ?

Merci pour votre réponse.

PS : je sais que l'objet passe bien en unusable, mais c'est la valeur dans user_objects qui m'interroge ...

[bouyao]

Sur metalink

The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.1.0.2
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal
An index rebuilt either Online or Offline.

Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster

So, the base table is not referred for data when the index is rebuilt offline.
This article describes this behavior with test cases and depicts a few scenarios when this is violated.

Fix
The test cases considers BTree index being rebuilt online/offline. The results are also same for Bitmap index. For analysis 10046 trace is generated to see if the statement refers the base table for data access. Trace Analyzer is also used to get a clear picture (Note: 224270.1).

Base Table: T5
Index Name: IND5

Test - 1:
=========
Index is rebuilt OFFLINE

alter session set events '10046 trace name context forever, level 12';
alter index ind5 rebuild;
alter session set events '10046 trace name context off';

The trace analyzer output has the following WAIT details:
+ There is no block access from T5
+ IND5 blocks are accessed

Test - 2:
=========
Index is rebuilt ONLINE:

alter session set events '10046 trace name context forever, level 12';
alter index ind5 rebuild online;
alter session set events '10046 trace name context off';

The trace analyzer output has the following WAIT details:
+ T5 blocks are accessed

CONCLUSION
==========
When an index is rebuilt offline there is no FTS on the base table. When index is rebuilt online all the blocks from the base table are accessed.

These conclusions are when we donot make an scenario when the index is unusable and then there
is data load to the base table, and finally the index is rebuilt. Lets see test results from different scenarios when index is unusable.


Test - 3
=========
Index is made unusable. Nodata is load to the base table.Index is rebuilt.

SQL> ALTER INDEX ind5 UNUSABLE;
Index altered.

SQL> select index_name,status from user_indexes where index_name = 'IND5';
INDEX_NAME STATUS
------------------------------ --------
IND5 UNUSABLE

alter session set events '10046 trace name context forever, level 12';
ALTER INDEX ind5 REBUILD;
alter session set events '10046 trace name context off';

SQL> select index_name,status from user_indexes where index_name = 'IND5';

INDEX_NAME STATUS
------------------------------ --------
IND5 VALID


The trace analyzer output has the following WAIT details:
+ There is no block access from T5
+ IND5 blocks are accessed

Test - 4:
=========
Index is made unusable. Data is load to the base table.Then Index is rebuilt OFFLINE.

SQL> ALTER INDEX ind5 UNUSABLE;
Index altered.

SQL> insert into t5 values(55555,'EEEEE');
insert into t5 values(55555,'EEEEE')
*
ERROR at line 1:
ORA-01502: index 'BH.IND5' or partition of such index is in unusable state

Test - 5:
=========
Now lets do some dataload using sqlldr.

Index made unusable / sqlldr dataload to table / rebuild index

load data
infile *
append
into table t5(
a position(1:5),
b position(6:10))

BEGINDATA
55555EEEEE
44444DDDDD
66666FFFFF

sqlldr userid=bh/sh control=test.ctl log=test.log bad=test.bad discard=test.discard skip=0

(skip_unusable_indexes = false -- DEFAULT)

all the three rows are listed in test.bad. NO DATA LOADED


Test - 6:
=========
Index made unusable / sqlldr dataload to table with skip_unusable_indexes=true / rebuild index

SQL> select count(*) from t5;
COUNT(*)
----------
0

SQL> create index ind5 on t5(b) storage(initial 1K next 1K maxextents unlimited pctincrease 0);
Index created.

SQL> select block_id,blocks from dba_extents where segment_name = 'T5';
BLOCK_ID BLOCKS
---------- ----------
5897 130

SQL> select block_id,blocks from dba_extents where segment_name = 'IND5';
BLOCK_ID BLOCKS
---------- ----------
4682 2

SQL> alter index ind5 unusable;
Index altered.

sqlldr userid=bh/sh control=test.ctl log=test.log bad=test.bad discard=test.discard skip_unusable_indexes=true

SQL> select count(*) from t5;
COUNT(*)
----------
154400

DATA GETS LOADED to table. Data doesnot go to index.

SQL> select block_id,blocks from dba_extents where segment_name = 'T5';
BLOCK_ID BLOCKS
---------- ----------
5897 130
4684 130
4814 195

SQL> select block_id,blocks from dba_extents where segment_name = 'IND5';
BLOCK_ID BLOCKS
--------- ----------
4682 2

No new blocks gets added to index.

SQL> select status,index_type from user_indexes where index_name = 'IND5';
STATUS
--------
UNUSABLE

alter session set events '10046 trace name context forever, level 12';
ALTER INDEX ind5 REBUILD;
alter session set events '10046 trace name context off';

SQL> select count(*) from dba_extents where segment_name = 'IND5';
COUNT(*)
----------
9

SQL> select status,index_type from user_indexes where index_name = 'IND5';
STATUS
--------
VALID

From trace analyzer o/p, there is trace that T5 has been referred.
+ < The base table blocks are accessed >

CONCLUSION
==========
As documentation says, while REBUILDing an index OFFLINE, the base table is not referred. But there are situations where the base table is accessed similar to an index create, they are:

+ index is made "unusable"
+ data is loaded with sql loader with skip_unusable_indexes = TRUE
+ index is REBUILD OFFLINE

Documentation is not in agreement in this particular case.

There could be one more scenarion I guess:
+ move a table to a different tablespace
+ index becomes unusable
+ we rebuild the index


Basically, when an index is made "unusable", it is normally meant that it will not be used at all (that is drop later) or it has to be drop/create. Here the index being unusable takes more priority than the Offline rebuild of it. An unusable index has to refer the base table while rebuilding.

[rouardg]

Bonjour,

Si tu déplaces une table via la commande ALTER TABLE ... MOVE, il est normal que les index soient UNUSABLE.

Car en déplaçant la table, le ROWID de chaque ligne de la table change, et comme l'index contient le ROWID, l'index n'est alors plus utilisable.

Pour remédirer à cela, tu dois reconstruire l'index, via un ALTER INDEX ... REBUILD.

[LeoAnderson]

La question du monsieur est "pourquoi dans user_indexes il apparait INVALID alors que dans user_objects il apparait VALID ?" pas "pourquoi mon index est invalide" ! ;-)

A mon avis, dans user_objects, l'index est valide dans le sens où sa définition n'est pas incohérente (la table et les colonnes existent toujours)
Dans user_index, le move a perturbé l'organisation "interne" de l'index (cf note de bouyao) et n'est plus utilisable.

j'espère avoir été clair ...

[orafrance]

Je suis d'accord avec Leo, l'index est correct, pas de violation d'unicité ou de colonne "bizarreé", en revanche comme le dit rouarg, les ROWID on changeait donc l'index est inutilisable.

Elémentaire mon cher Watson

[bouyao]

Tout simplement la table est vide.

[orafrance]

donc les ROWID on changeait... il n'y en a plus dans la table

[jokos2000]

Merci pour vos réponses

La table n'est pas vide.

Donc si je résume :

user_indexes ==> l'index est-il fonctionnellement viable ?
user_objects ==> l'index est-il structurellement viable ?

C'est çà ?

[bouyao]

Est ce que la table est partitionné ?

[jokos2000]

Elle n'est pas non plus partitionnée.

C'est une table tout ce qu'il y a de plus basique avec 3 champs et un index, avec 3 enregistrements.