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
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.
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.
================================================================================================
Comments
Post a Comment
Oracle DBA Information