Which index can you rebuild?

I recently wrote on table reorg and rebuild index

Rule number one : you cannot rebuild a partitioned index in whole. You need to rebuild each individual (sub-)partition

Rule number two : to rebuild an iot, move the table instead of trying to rebuild the underlying index

Rule number three : a LOB index is not really an index. Do not rebuild this

Rule number four : a NOSEGMENT index is not a supported type of index, but it may appear in your user_objects list. It is used internally by OEM and other tuning tools to do a what-if calculation on the explain plan. It is not listed in USER_INDEXES. Do not rebuild this

Test case :

SQL> CREATE CLUSTER c(x NUMBER);

Cluster created.

SQL> CREATE INDEX a01
2 ON CLUSTER c;

Index created.

SQL> CREATE TABLE t
2 (
3 p NUMBER PRIMARY KEY,
4 a01 NUMBER,
5 a02 NUMBER,
6 a03 NUMBER,
7 a04 NUMBER,
8 a05 NUMBER,
9 a06 NUMBER,
10 a07 VARCHAR2 (40),
11 a08 CLOB
12 );

Table created.

SQL> CREATE INDEX a02
2 ON t (a01);

Index created.

SQL> CREATE INDEX a03
2 ON t (a02)
3 REVERSE;

Index created.

SQL> CREATE INDEX a04
2 ON t (SQRT (a01));

Index created.

SQL> CREATE INDEX a05
2 ON t (COS (a01))
3 REVERSE;

Index created.

SQL> CREATE BITMAP INDEX a06
2 ON t (a03);

Index created.

SQL> CREATE BITMAP INDEX a07
2 ON t (SIGN (a04));

Index created.

SQL> CREATE INDEX a08
2 ON t (a07)
3 INDEXTYPE IS ctxsys.context;

Index created.

SQL> CREATE INDEX a09
2 ON t (a05)
3 GLOBAL PARTITION BY HASH (a05)
4 (PARTITION p);

Index created.

SQL> CREATE TABLE i (x NUMBER CONSTRAINT A10 PRIMARY KEY)
2 ORGANIZATION INDEX;

Table created.

SQL> CREATE INDEX A11 on T(A06) NOSEGMENT;

Index created.

SQL> SELECT index_name,
2 index_type,
3 partitioned,
4 generated
5 FROM user_indexes
6 ORDER BY 1;

INDEX_NAME INDEX_TYPE PAR G
------------------------------ --------------------------- --- -
A01 CLUSTER NO N
A02 NORMAL NO N
A03 NORMAL/REV NO N
A04 FUNCTION-BASED NORMAL NO N
A05 FUNCTION-BASED NORMAL/REV NO N
A06 BITMAP NO N
A07 FUNCTION-BASED BITMAP NO N
A08 DOMAIN NO N
A09 NORMAL YES N
A10 IOT - TOP NO N
DR$A08$X NORMAL NO N
SYS_C009276 NORMAL NO Y
SYS_IL0000028076C00009$$ LOB NO Y
SYS_IL0000028087C00006$$ LOB NO Y
SYS_IL0000028092C00002$$ LOB NO Y
SYS_IOT_TOP_28090 IOT - TOP NO Y
SYS_IOT_TOP_28095 IOT - TOP NO Y

SQL> ALTER INDEX a01 REBUILD;

Index altered.

SQL> ALTER INDEX a02 REBUILD;

Index altered.

SQL> ALTER INDEX a03 REBUILD;

Index altered.

SQL> ALTER INDEX a04 REBUILD;

Index altered.

SQL> ALTER INDEX a05 REBUILD;

Index altered.

SQL> ALTER INDEX a06 REBUILD;

Index altered.

SQL> ALTER INDEX a07 REBUILD;

Index altered.

SQL> ALTER INDEX a08 REBUILD;

Index altered.

SQL> ALTER INDEX a09 REBUILD;
ALTER INDEX a09 REBUILD
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX a09 REBUILD PARTITION P;

Index altered.

SQL> ALTER INDEX a10 REBUILD;
ALTER INDEX a10 REBUILD
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

SQL> ALTER TABLE i MOVE;

Table altered.

SQL> ALTER INDEX A11 REBUILD;
ALTER INDEX A11 REBUILD
*
ERROR at line 1:
ORA-08114: can not alter a fake index

SQL> ALTER INDEX SYS_IL0000028076C00009$$ REBUILD;
ALTER INDEX SYS_IL0000028076C00009$$ REBUILD
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

A function-based domain index should be rebuildable too, I have not tested this for you

Leave a Reply

Your email address will not be published.