Home > Oracle, Performance > Exchange Partition and update global indexes

Exchange Partition and update global indexes


What is the best way to exchange partition with other tables?  What is the impact of using UPDATE GLOBAL INDEXES? And is it always evil?  Its all depends on case to case. 

Case 1:  When exchange more than 15% – 20% of existing data.

Here is the test case.  I have  a table with 4,59,327  rows as partitioned below.

SQL> create table Test(
  2  owner varchar2(30),
  3  object_name varchar2(128),
  4  object_id number)
  5  PARTITION BY RANGE (object_id)
  6  (PARTITION P1 values less than (1000),
  7  PARTITION P2 values less than (2000),
  8  PARTITION P3 values less than (5000),
  9  PARTITION P4 values less than (maxvalue));

Table created.

SQL> insert into test  select owner,object_name,object_id from dba_objects;

12022 rows created.

SQL>  insert into test select * from test partition (p4);

14200 rows created.

SQL>  insert into test select * from test partition (p4);

28400 rows created.

SQL>  insert into test select * from test partition (p4);

56800 rows created.

SQL> commit ;

Commit complete.

I have created the table such way that one partition got maximum number of records and others are relatively small.

SQL>  select count(*) from test partition (p4);

  COUNT(*)
———-
    454400

SQL> select count(*) from test partition (p3);

  COUNT(*)
———-
      2994

SQL> select count(*) from test partition (p2);

  COUNT(*)
———-
       993

SQL> select count(*) from test partition (p1);

  COUNT(*)
———-
       940

This test case is mainly for the impact of  UPDATE GLOBAL indexes on exchange partition, so created 3 global indexes on the partitioned table. 

SQL> create index i_object_name  on  test(object_name);

Index created.
SQL> create index i_object_id on test(object_id);

Index created.
SQL> create index i_owner on test(owner);

Index created.

To hold the intermediate data, i have created a hash table identical to TEST and moved the partition P4 data to the intermediate table TEST1. 

SQL> create table test1  as select * from test where 1 = 0;

Table created.

SQL> ALTER TABLE test EXCHANGE PARTITION P4 WITH TABLE test1;

Table altered.

Now the test environment is ready.  I will do the exchange partition first with the UPDATE GLOBAL indexes.  As expected it took a long time as it need to update all the global index entries.

 
SQL> ALTER TABLE test EXCHANGE PARTITION P4 WITH TABLE test1 update global indexes;

Table altered.

Elapsed: 00:03:09.88

It took  3.09.88 minutes to complete.   And i have re-built the indexes ( Note:  This is for a specific case, in normal case index re-build is NOT required as we exchanged  with UPDATE GLOBAL indexes).

SQL> alter index I_OWNER rebuild;

Index altered.

Elapsed: 00:00:10.99
SQL> alter index I_OBJECT_NAME  rebuild;

Index altered.

Elapsed: 00:00:11.07
SQL> alter index I_OBJECT_ID rebuild;

Index altered.

Elapsed: 00:00:16.62

Now the total time taken for exchange partition operation is 3.09.88  with out index re-build and with index re-build is 3.47 minutes.  Is there any better way to do this exchange partition?  Why we need to exchange partition with UPDATE GLOBAL indexes as it is a costly operation?    I reversed the data again and did the test again.  

SQL> ALTER TABLE test EXCHANGE PARTITION P4 WITH TABLE test1;

Table altered.

Elapsed: 00:00:00.54
Elapsed: 00:00:00.00
SQL> alter index I_OWNER rebuild parallel 3 nologging;

Index altered.

Elapsed: 00:00:07.51
SQL>  alter index I_OBJECT_NAME  rebuild parallel 3 nologging;

Index altered.

Elapsed: 00:00:11.30
SQL> alter index I_OBJECT_ID rebuild  parallel 3 nologging;

Index altered.

Elapsed: 00:00:06.79

The total time taken to complete the overall operation was 0.26 minutes including index-rebuild just 13% of the UPDATE GLOBAL indexes case.  It is a big difference – not just the time, but in resource usage as well.

Case 2: When exchanging less than 10% of table data.

Is the above case is always valid and give abetter performance?  Not really.   In the avove example, it took 0.26 minutes to complete the exchange p artition  – with NO update global indexs and rebuld indexes.  But, when we have less data to upload, then UPDATE GLOBAL indexes is always better. 

SQL> ALTER TABLE test EXCHANGE PARTITION P1 WITH TABLE test1 update global indexes;

Table altered.

Elapsed: 00:00:00.12
SQL> select count(*) from test partition (p1);

  COUNT(*)
———-
       940

Elapsed: 00:00:00.00

It just took just 0.12 seconds to complete the exchange partition.   So, no option is better or bad, it is all depends on what amount of data you are playing with and take right decision.   So, know your data first then technology.

Advertisements
  1. manish
    November 20, 2014 at 8:50 pm

    thanks .. very handy test case to establish the behaviour. ANother thing could be to test the exchange with parallelism

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: