Document Type | Technical Information
Category | Tuning
Document Number | TTUTI020
Overview
This explains the online option that allows DML operations on the corresponding table while creating an index (Create index). Until the index creation is completed, all DML is possible in offline mode.
Method
Example of performing Create index online
When creating an index during online
<session 1> SQL> create table a (a number); Table created. SQL> insert into a select level from dual connect by level <= 100000; 100000 rows created. SQL> commit; Commit complete. SQL> insert into a values (100001); 1 row created. <session 2> SQL> create index idx_a on a (a); create index idx_a on a (a) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired <-- error occurred
When creating an index online
<session 1>
SQL> create table a (a number);
Table 'A' created.
SQL> insert into a select level from dual connect by level <= 100000;
100000 rows inserted.
SQL> commit;
Commit completed.
SQL> select count(*) from a;
COUNT(*)
---------
100000
1 row selected.
SQL> insert into a values (100001);
1 row inserted.
<session 2>
SQL> create index idx_a on a (a) online;
<session 1>
SQL> commit;
Commit completed.
<session 2>
Index 'IDX_A' created. <-- index created
SQL> set autot on
SQL> select /*+ index(a) */ count() from a where a < 1000000;
COUNT(*)
---------
100001
1 row selected.
SQL ID: 266
Plan Hash Value: 1910050088
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:225, %CPU:0, Rows:1)
2 SORT AGGR (Cost:225, %CPU:0, Rows:1)
3 INDEX (RANGE SCAN): IDX_A (Cost:225, %CPU:0, Rows:126912)
Predicate Information
--------------------------------------------------------------------------------
3 - access: ("A"."A" < 1000000) (1.000)
Note