Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far?

Question

In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning. Beside local indexes, there are two global indexes on it. Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid.

Next night, the feature called Asynchronous Global Index Maintenance background job has started an ALTER INDEX xxx COALESCE CLEANUP command, which runs since several days and cleans up the global indexes from orphan entries of dropped partitions.

Unfortunately, we need to create another index on this table with different parameters, but it's not possible due to resource busy error because of a shared DML lock (Row-X (SX)) caused by the background job. Since we need to deploy a new release, we need definitely the DDL on another index. For me it looks strange, that alter index ... coalesce cleanup doesn't allow to create another index in parallel. Why should there be any relation?

Question: If we kill the session, will the work of past 2 days be lost and job need to restart again from beginning?

Answer 1

For obvious reasons (laziness) I used the example for creating the table and index from this post:

https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/

create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue));
insert into muse with g as (select * from dual connect by level <= 1000) select rownum, mod(rownum,100000), 'DAVID BOWIE' from g,g,g where rownum <=  3000000;
commit;
create index muse_id_i on muse(id);
create index muse_code_i on muse(code) global partition by range(code)(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

Next drop a partition:

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

Then analyze the index:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         3000000     1000000

Orphaned entries are presented here as deleted entries.

Here comes my addition. Start the coalesce cleanup manually, then interrupt it in a few seconds (Ctrl-C), then analyze the index again:

SQL> alter index muse_id_i coalesce cleanup;
^C
alter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL> SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2723063      723063

As you can see, even with interrupting the command, some of the entries were cleaned up. Now do it again:

SQL> alter index muse_id_i coalesce cleanup;
^Calter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2532434      532434

Next create an index then continue:

SQL> create index muse_name_i on muse(name) local;

Index created.

SQL> alter index muse_id_i coalesce cleanup;
^C
alter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2151894      151894

Finally:

SQL> alter index muse_id_i coalesce cleanup;

Index altered.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2000000           0

SQL>

So no, progess was not lost.