update global indexes: truncate partition vs exchange partition

1.output.sql

SQL> create table tpart (a,b)
  2    partition by range(a)
  3      (
  4         partition p1 values less than (1),
  5         partition p2 values less than (2),
  6         partition p3 values less than (3)
  7      )
  8  as select mod(n,3), n from xmltable('1 to 1000' columns n for ordinality);

Table created.

SQL> create index ix_part on tpart(a,b) global;

Index created.

SQL> select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

INDEX_NAME STATUS   ORP
---------- -------- ---
IX_PART    VALID    NO

1 row selected.

SQL> create table tp3_ex as select * from tpart partition(p3);

Table created.

SQL> alter table tpart truncate partition p2 update global indexes;

Table truncated.

SQL> select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

INDEX_NAME STATUS   ORP
---------- -------- ---
IX_PART    VALID    YES

1 row selected.

SQL> begin dbms_part.cleanup_gidx(user,'TPART'); end;
  2  /

PL/SQL procedure successfully completed.

SQL> select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

INDEX_NAME STATUS   ORP
---------- -------- ---
IX_PART    VALID    NO

1 row selected.

SQL> alter table tpart exchange partition p3 with table tp3_ex update global indexes;

Table altered.

SQL> select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

INDEX_NAME STATUS   ORP
---------- -------- ---
IX_PART    VALID    NO

1 row selected.

2.test-case.sql

set echo on feed on;
col index_name for a10;

create table tpart (a,b)
  partition by range(a)
    (
       partition p1 values less than (1),
       partition p2 values less than (2),
       partition p3 values less than (3)
    )
as select mod(n,3), n from xmltable('1 to 1000' columns n for ordinality);
create index ix_part on tpart(a,b) global;
select index_name,status,orphaned_entries from user_indexes where table_name='TPART';
create table tp3_ex as select * from tpart partition(p3);
alter table tpart truncate partition p2 update global indexes;
select index_name,status,orphaned_entries from user_indexes where table_name='TPART';
begin dbms_part.cleanup_gidx(user,'TPART'); end;
/
select index_name,status,orphaned_entries from user_indexes where table_name='TPART';
alter table tpart exchange partition p3 with table tp3_ex update global indexes;
select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

drop table tpart purge;
drop table tp3_ex purge;
set echo off;

3.move.output.sql

SQL> create table tpart (a,b)
  2    partition by range(a)
  3      (
  4         partition p1 values less than (1),
  5         partition p2 values less than (2),
  6         partition p3 values less than (3)
  7      )
  8  as select mod(n,3), n from xmltable('1 to 1000' columns n for ordinality);

Table created.

SQL> create index ix_part on tpart(a,b) global;

Index created.

SQL> select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

INDEX_NAME STATUS   ORP
---------- -------- ---
IX_PART    VALID    NO

1 row selected.

SQL> alter table tpart move partition p2 update global indexes;

Table altered.

SQL> select index_name,status,orphaned_entries from user_indexes where table_name='TPART';

INDEX_NAME STATUS   ORP
---------- -------- ---
IX_PART    VALID    YES

1 row selected.