exchange partition update global index

 
There is a test table with range partition. There is an global index on this table.   The global index is huge  - about 200 GB.

When I  set "alter table exchange partition part_1... update all indexes"   this  index is going to rebuild.
The question is Database is reading all index's segments and rebuilding them entirely  or Database is reading only index data corresponding moving partiotion 
(not all index's segments).

From this point if I need to finish "exchange partition"(online) quickly Should I use smaller interval for range?

Hi Greg,   An exchange operation doesnt actually move data, it just plugs the exchange table into the data dictionary and unplugs the partition that was exchanged.
As long as you use WITHOUT VALIDATION, that part will be quick.   But when you use the UPDATE ALL INDEXES, for a global index, it will act on a row-by-row basis 
for the rows being exchanged.   If the table partition is populated and the exchange table is empty, then it will find and delete each row from the global index
 that corresponds to the existing rows in the partition that is being exchanged out.  If the partition is empty and the exchange table has rows, it will insert
  rows into the global index corresponding to the rows in the exchange table.   If the indexes are local, then its just a data dictionary swap again for the local 
  index, as with the partition itself.  Thats why when you exchange on a partition that has local indexes and you use the UPDATE ALL INDEXES clause, 
  you must have a corresponding index on the exchange table for every local index on the partitioned table.

  
  

I have not found anywhere in the documentation or on metalink that as you read database just delete global index data corresponding my exchange partition and 
this operation is not the same as the operation ALTER INDEX .. REBUILD ONLINE.
In my case I have high loading OLTP system with huge tables and Im afraid that the "exchange partition" operation with rebuild huge 2-3 indexes online 
will take 1-2 days
Also I consider to use global partition indexes instead of global non-partition for primary key goal (not real PK but an unique index for ID column) .

Hi Greg,   I have never been found 'update all indexes' to be valid syntax.    You should use  something like
alter table mypart exchange partition p2 with table mypart_exch including indexes without validation update global indexes;

If you leave off the 'update global indexes' the global indexes will be invalid after you do the PMOPS, and you will have to rebuild it online.   
  But the 'update global indexes' clause causes the index to be maintained during the PMOP so the whole index doesnt have to be rebuilt.   

See notes
Information Center: Partitioning in the Oracle Database [ID 1477997.2]
   Exchange Partition Having Global Index with Non - Partitioned Table [ID 419505.1]
   Update Global Indexes" Allows Automatic Global Index Maintenance During DDL [ID 139707.1] -- 
   do check this note for the exchange partition, because it shows if you create a global index on the non-partitioned exchange table (dont suggest doing this), 
   then when it exchanges it is invalid, even if you do use the 'update global indexes' clause.  So, not something you would want to do.

Below is a testcase.  Here were my timings.
SQL v11203> set timing on
SQL v11203> alter table mypart exchange partition p2 with table mypart_exch including indexes without validation update global indexes;

Table altered.

Elapsed: 00:00:00.03   <===== time for exchange is less than half the time to rebuild the index online

SQL v11203> alter index mypart_idx rebuild online;

Index altered.

Elapsed: 00:00:00.07

Heres the quick  testcase:

create table mypart (id number, name varchar2(10), state varchar2(2))
partition by range (id)
(partition p1 values less than (10),
 partition p2 values less than (20),
 partition p3 values less than (30))
/

create index mypart_idx on mypart(state);

----------------------------分区表上建global index 对exchange partition 没任何影响，看不到这个global index 的，但是如果有partition index  加上了including indexes，就需要对比swap表上有无index了

create table mypart_exch as select * from mypart where 1=0;

insert into mypart values (9, 'Susan', 'CO');
insert into mypart values (29, 'Nancy', 'WA');
insert into mypart_exch values (19, 'Joan', 'NM');
commit;

-- see we have no rows in p2, and we have 1 row in the exchange table that will be swapped for p2
select count (*) from mypart partition (p1);
select count (*) from mypart partition (p2);
select count (*) from mypart partition (p3);
select count (*) from mypart_exch;

select table_name, index_name, status, index_type, partitioned from dba_indexes where table_name = 'MYPART';

TABLE_NAME                     INDEX_NAME                     STATUS   INDEX_TYPE                  PAR
------------------------------ ------------------------------ -------- --------------------------- ---
MYPART                         MYPART_IDX                     VALID    NORMAL                      NO

alter table mypart exchange partition p2 with table mypart_exch including indexes without validation update global indexes;

---index partition index要一致
alter table mypart exchange partition p2 with table mypart_exch   without validation update global indexes;

----不看index， global的还是好的

alter table mypart exchange partition p2 with table mypart_exch   without validation；

----不看index，两边index都是同时unsable了，就算再加上update global indexes;  global indexes也好不了，因为只维护当前的分区index是不够的，Oracle不知道要全部rebuild

---global index一旦坏了后，update global indexes;不能在变好了。

select *from mypart  partition (p2);
select *from  mypart_exch
 
select table_name, index_name, status, index_type, partitioned from dba_indexes where table_name like 'MYPART%';
select   index_name, status,   c.partition_name from dba_ind_partitions  c where index_name like 'MYPART%'
alter index MYPART_IDX rebuild;
alter index MYPART_IDX3 rebuild;
insert into mypart values (9, 'Susan', 'CO');
insert into mypart values (29, 'Nancy', 'WA');
insert into mypart_exch values (19, 'Joan', 'NM');

TABLE_NAME                     INDEX_NAME                     STATUS   INDEX_TYPE                  PAR
------------------------------ ------------------------------ -------- --------------------------- ---
MYPART                         MYPART_IDX                     VALID    NORMAL                      NO

 

 

--global index一旦unusable，update global indexes;也不能再usable。

SQL> alter index MYPART_IDX unusable;
 select table_name, index_name, status, index_type, partitioned from dba_indexes where table_name like 'MYPART%';

TABLE_NAME             INDEX_NAME        STATUS   INDEX_TYPE        PARTITIONED

MYPART                     MYPART_IDX           UNUSABLE NORMAL                      NO

 
insert into mypart values (9, 'Susan', 'CO');
insert into mypart values (29, 'Nancy', 'WA');
insert into mypart_exch values (19, 'Joan', 'NM'); 
 
SQL> alter table mypart truncate  partition p2 update global indexes;

Table truncated

SQL> select table_name, index_name, status, index_type, partitioned from dba_indexes where table_name like 'MYPART%';

TABLE_NAME               INDEX_NAME       STATUS   INDEX_TYPE          PARTITIONED
 
MYPART                     MYPART_IDX       UNUSABLE NORMAL                      NO

 

---global index是好的，truncate 加不加update global indexes 也不会变unusable

SQL> alter index MYPART_IDX rebuild;

Index altered

SQL> select table_name, index_name, status, index_type, partitioned from dba_indexes where table_name like 'MYPART%';

TABLE_NAME           INDEX_NAME              STATUS   INDEX_TYPE        PARTITIONED
 
MYPART              MYPART_IDX           VALID    NORMAL                      NO
 
SQL> alter table mypart truncate  partition p2;

Table truncated

SQL> select table_name, index_name, status, index_type, partitioned from dba_indexes where table_name like 'MYPART%';

TABLE_NAME          INDEX_NAME        STATUS   INDEX_TYPE     PARTITIONED
 
MYPART            MYPART_IDX              VALID     NORMAL                      NO