28 | 03 | 2024
Latest Articles
Popular Articles

Administration

How to create indexes on big tables, fast index creation with the unusable attribute

User Rating:  / 0
PoorBest 

How to create indexes on big tables, fast index creation with the unusable attribute.



A table is partitoned and huge. We would like to create online an additional index. We would like to avoid any downtime. The database nearly operates 24x7

1. We verify whether skip_unusable_indexes = true ( which is the default value )
2. We can create an index initially with the atribute UNUSABLE. Index - metadata - creation is very very fast. ( but index is not usable at all )
3. We can rebuild online the index ( partitions )


In the below example is

SESSION A the user who creates and populates (DML) a big table MY_OBJECTS
SESSION B the user who creates the index MY_OBJECTS_IDX1 on table MY_OBJECTS
SESSION C the DBA session.

 


SQL_SESSION C> connect / as sysdba
Connected.
SQL> show parameter skip_

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes             boolean     TRUE


SQL_SESSION_A > create table my_objects ( OWNER    VARCHAR2(30) NOT NULL,
OBJECT_NAME     VARCHAR2(30) NOT NULL,
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID     NUMBER NOT NULL,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE     VARCHAR2(19),
CREATED     DATE NOT NULL,
LAST_DDL_TIME     DATE NOT NULL,
TIMESTAMP    VARCHAR2(19),
STATUS     VARCHAR2(7),
TEMPORARY    VARCHAR2(1),
GENERATED    VARCHAR2(1),
SECONDARY    VARCHAR2(1),
NAMESPACE    NUMBER NOT NULL,
EDITION_NAME    VARCHAR2(30) ) partition by hash (object_id) partitions 32;


Table created.


SQL_SESSION_A > insert into my_objects select * from all_objects;

54549 rows created.

SQL_SESSION_A > commit;

Commit complete.

SQL_SESSION_A > insert into my_objects select * from all_objects;
54549 rows created. -- WITHOUT COMMIT.



Other session, tries to create an index in that table, but since this requires an exclusive local it fails, even with the unusable attribute
However index - metadata - creation with the unusable attribute is really very very fast.

 


SQL_SESSION_B > create index MY_OBJECTS_IDX1 on my_objects (object_id) local unusable;
create index MY_OBJECTS_IDX1 on my_objects (object_id) local unusable
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL_SESSION_A > insert into my_objects select a.* from all_objects a,all_objects b where rownum < 1000000;

999999 rows created.

SQL_SESSION_A > commit;

Commit complete.

SQL_SESSION_B > create index MY_OBJECTS_IDX1 on my_objects (object_id) local unusable;

Index created.

 



Afterwards the index partitions are unusable but there is no failing query / DML as long as the instance initialization parameter skip_unusable_indexes = true



SQL_SESSION_B > select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME               PARTITION_NAME              STATUS
------------------------------ ------------------------------ --------
MY_OBJECTS_IDX1            SYS_P220               UNUSABLE
MY_OBJECTS_IDX1            SYS_P219               UNUSABLE
MY_OBJECTS_IDX1            SYS_P218               UNUSABLE
MY_OBJECTS_IDX1            SYS_P217               UNUSABLE
MY_OBJECTS_IDX1            SYS_P216               UNUSABLE
MY_OBJECTS_IDX1            SYS_P215               UNUSABLE
MY_OBJECTS_IDX1            SYS_P214               UNUSABLE
MY_OBJECTS_IDX1            SYS_P213               UNUSABLE
MY_OBJECTS_IDX1            SYS_P212               UNUSABLE
MY_OBJECTS_IDX1            SYS_P211               UNUSABLE
MY_OBJECTS_IDX1            SYS_P210               UNUSABLE
MY_OBJECTS_IDX1            SYS_P209               UNUSABLE
MY_OBJECTS_IDX1            SYS_P208               UNUSABLE
MY_OBJECTS_IDX1            SYS_P207               UNUSABLE
MY_OBJECTS_IDX1            SYS_P206               UNUSABLE
MY_OBJECTS_IDX1            SYS_P205               UNUSABLE
MY_OBJECTS_IDX1            SYS_P204               UNUSABLE
MY_OBJECTS_IDX1            SYS_P203               UNUSABLE
MY_OBJECTS_IDX1            SYS_P202               UNUSABLE
MY_OBJECTS_IDX1            SYS_P201               UNUSABLE
MY_OBJECTS_IDX1            SYS_P200               UNUSABLE
MY_OBJECTS_IDX1            SYS_P199               UNUSABLE
MY_OBJECTS_IDX1            SYS_P198               UNUSABLE
MY_OBJECTS_IDX1            SYS_P197               UNUSABLE
MY_OBJECTS_IDX1            SYS_P196               UNUSABLE
MY_OBJECTS_IDX1            SYS_P195               UNUSABLE
MY_OBJECTS_IDX1            SYS_P194               UNUSABLE
MY_OBJECTS_IDX1            SYS_P193               UNUSABLE
MY_OBJECTS_IDX1            SYS_P192               UNUSABLE
MY_OBJECTS_IDX1            SYS_P191               UNUSABLE
MY_OBJECTS_IDX1            SYS_P190               UNUSABLE
MY_OBJECTS_IDX1            SYS_P189               UNUSABLE

32 rows selected.


SQL_SESSION_A > insert into my_objects select * from all_objects;

54582 rows created.

SQL_SESSION_A > commit;

Commit complete.


SQL_SESSION_A > insert into my_objects select * from all_objects;

54582 rows created. -- no commit.

 

 


Rebuild of index partition can temporary be delayed with a TX row lock contention wait. ( but we don' t care )

 

 


SQL_SESSION_B > alter index MY_OBJECTS_IDX1 rebuild partition SYS_P220 online;

SQL_SESSION_C > set pagesize 9999
SQL_SESSION_C > select username,event from v$session where username is not null;

USERNAME
------------------------------
EVENT
----------------------------------------------------------------
PUBLIC
SQL*Net message from client

YORICK
enq: TX - row lock contention

YORICK
SQL*Net message from client

SYS
SQL*Net message to client




SQL_SESSION_A > commit;

Commit complete.

 


As soon as there are no active transactions anymore the index rebuild operation terminates.





SQL_SESSION_B > alter index MY_OBJECTS_IDX1 rebuild partition SYS_P220 online;

Index altered.


Online rebuild of all the index partitions. on going short transactions can possible delay the index rebuild ... but who cares ( as long as we speak about short transactions )


SQL> select 'alter index MY_OBJECTS_IDX1 rebuild partition '||PARTITION_NAME||' online;' from user_ind_partitions where index_name='MY_OBJECTS_IDX1';


SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME               PARTITION_NAME              STATUS
------------------------------ ------------------------------ --------
MY_OBJECTS_IDX1            SYS_P207               USABLE
MY_OBJECTS_IDX1            SYS_P208               USABLE
MY_OBJECTS_IDX1            SYS_P209               USABLE
MY_OBJECTS_IDX1            SYS_P210               USABLE
MY_OBJECTS_IDX1            SYS_P211               USABLE
MY_OBJECTS_IDX1            SYS_P212               USABLE
MY_OBJECTS_IDX1            SYS_P213               USABLE
MY_OBJECTS_IDX1            SYS_P214               USABLE
MY_OBJECTS_IDX1            SYS_P215               USABLE
MY_OBJECTS_IDX1            SYS_P216               USABLE
MY_OBJECTS_IDX1            SYS_P217               USABLE
MY_OBJECTS_IDX1            SYS_P218               USABLE
MY_OBJECTS_IDX1            SYS_P219               USABLE
MY_OBJECTS_IDX1            SYS_P220               USABLE
MY_OBJECTS_IDX1            SYS_P189               USABLE
MY_OBJECTS_IDX1            SYS_P190               USABLE
MY_OBJECTS_IDX1            SYS_P191               USABLE
MY_OBJECTS_IDX1            SYS_P192               USABLE
MY_OBJECTS_IDX1            SYS_P193               USABLE
MY_OBJECTS_IDX1            SYS_P194               USABLE
MY_OBJECTS_IDX1            SYS_P195               USABLE
MY_OBJECTS_IDX1            SYS_P196               USABLE
MY_OBJECTS_IDX1            SYS_P197               USABLE
MY_OBJECTS_IDX1            SYS_P198               USABLE
MY_OBJECTS_IDX1            SYS_P199               USABLE
MY_OBJECTS_IDX1            SYS_P200               USABLE
MY_OBJECTS_IDX1            SYS_P201               USABLE
MY_OBJECTS_IDX1            SYS_P202               USABLE
MY_OBJECTS_IDX1            SYS_P203               USABLE
MY_OBJECTS_IDX1            SYS_P204               USABLE
MY_OBJECTS_IDX1            SYS_P205               USABLE
MY_OBJECTS_IDX1            SYS_P206               USABLE

32 rows selected.