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.