Thanks for the question, Brian.

Asked: April 21, 2016 - 1:31 pm UTC

Last updated: January 17, 2017 - 11:40 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Gentlemen,

I am currently moving historical partitions out of a "current" schema (IBTRESDBA)
into an "historical" schema (IBTRESDBA_HIST) using Oracle 11g.
There are 3 tables involved, a "parent" (APNTMT) that is RANGE partitioned (monthly),
and two "child" tables that are REFERENCE partitioned. I use EXCHANGE
partition on the "child" tables first, then I use the following DDL
on the parent:

alter table IBTRESDBA.APNTMT exchange partition Y14M03
with table IBTRESDBA_HIST.EXTAB_000_000_609
including indexes without validation update indexes;

The DDL above completes in 1 minute and 23 seconds.

The APNTMT (parent) table has a total of 20 indexes, 11 of these are LOCAL and
the rest are GLOBAL. I don't really need to move the indexes into
the historical schema, and would like to improve the performance of
the exchange DDL. However, I would like all the indexes in the current
schema (IBTRESDBA) to remain USABLE.

Would removing the INCLUDING INDEXES clause give me the performance boost
I am looking for and leave all of the current schema indexes usable?

Neither the 11g SQL Reference nor the VLDB & Partition Guide documentation
was clear on this. I am going to give it a try in my test schema but was
just looking for your input before I do so.

Thanks in advance.

and Chris said...

Think about it. You've just removed a bunch of rows from the table. If you don't update the indexes, they will point to non-existent rows.

So you need to update the indexes, otherwise they will be invalid after the exchange:

create table t (
  x int,
 y int
);

create table tpart (
  x int,
 y int
) partition by range (x) (
  partition p0 values less than (10),
 partition p1 values less than (20)
);

insert into tpart
  with rws as (
   select mod(rownum, 20), rownum from dual connect by level < 20
 )
  select r1.* from rws r1;

commit;

create index ig on tpart(y);

alter table tpart exchange partition p0 with table t;

select index_name, status from user_indexes
where  table_name like 'T%'
and    index_name like 'I%';

INDEX_NAME                     STATUS 
------------------------------ --------
IG                             UNUSABLE

If you don't need the indexes on your history table, you could try excluding indexes from the exchange. This may speed things up:

SQL> truncate table t ;

Table truncated.

Elapsed: 00:00:00.29
SQL> truncate table tpart ;

Table truncated.

Elapsed: 00:00:35.84
SQL> insert into tpart
  2    with rws as (
  3       select mod(rownum, 20), rownum from dual connect by level < 10000
  4     )
  5    select r1.* from rws r1, rws
  6     where  rownum <= 5000000;

5000000 rows created.

Elapsed: 00:01:19.07
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.24
SQL> alter table tpart exchange partition p0 with table t
  2   including indexes
  3   update indexes;

Table altered.

Elapsed: 00:00:28.05
SQL> truncate table t ;

Table truncated.

Elapsed: 00:00:04.34
SQL> truncate table tpart ;

Table truncated.

Elapsed: 00:00:34.46
SQL> insert into tpart
  2    with rws as (
  3       select mod(rownum, 20), rownum from dual connect by level < 10000
  4     )
  5    select r1.* from rws r1, rws
  6     where  rownum <= 5000000;

5000000 rows created.

Elapsed: 00:01:02.21
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.24
SQL>
SQL> alter table tpart exchange partition p0 with table t
  2   excluding indexes
  3   update indexes;

Table altered.

Elapsed: 00:00:21.86

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Test of modified DDL

Brian Blades, April 21, 2016 - 3:50 pm UTC

I modified the DDL by removing the INCLUDING INDEXES and changing UPDATE INDEXES to just UPDATE GLOBAL INDEXES as shown below:

alter table IBTRESDBA.APNTMT exchange partition Y14M05
with table IBTRESDBA_HIST.EXTAB_000_001_651
without validation update global indexes

EXCHANGE PARTITION Elapsed: +0 00:01:28.508280

All indexes were usable after the DDL, but there didn't seem
to be any performance improvement.

April 22, 2016 - 12:21 pm UTC

What problem is this running for over a minute causing?

Is this actually affecting other sessions, or are you just trying to get this "as fast as possible"?

If this is monthly partitioned, you're only running this every 30 days or so. Unless this is causing major issues it's probably good enough. Beware "compulsive tuning disorder".

Final Post

Brian Blades, April 22, 2016 - 12:35 pm UTC

You're right, the timing is acceptable and not CTD. Just trying to understand where all the time is going, since the EXCHANGE is mainly manipulating data dictionary entries and not actually move much data around. Perhaps it's the maintain global indexes.

Thanks for the help.

April 22, 2016 - 12:45 pm UTC

Well you've got 20 indexes, so if the partition is "large" it's going to take a while to update them all.

Try tracing the session if you really want to see what's going on.

Trace of Resource Consumption

Brian Blades, April 25, 2016 - 12:20 pm UTC

I did as you suggested and traced the EXCHANGE partition. The two processes that were consuming the most time were the following. Looks like it's related to global index maintenance.

SQL ID: c42mb0zhvy96f Plan Hash: 3260806256

insert /*+ RELATIONAL("TST_INSTNC") PARALLEL("TST_INSTNC",1) APPEND
NESTED_TABLE_SET_SETID NO_REF_CASCADE */ into "IBTRESDBA"."TST_INSTNC"
partition ("Y14M07") (select /*+ RELATIONAL("TST_INSTNC")
PARALLEL("TST_INSTNC",1) */ * from "IBTRESDBA"."TST_INSTNC" partition
("Y14M07") union all select /*+ RELATIONAL("EXTAB_000_001_903")
PARALLEL("EXTAB_000_001_903",1) */ * from
"IBTRESDBA_HIST"."EXTAB_000_001_903") insert global indexes

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 1 0 0
Execute 1 34.13 86.25 112699 73383 248130 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 34.14 86.28 112699 73384 248130 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 221 (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT (cr=73383 pr=112699 pw=30221 time=86257831 us)
2353979 2353979 2353979 UNION-ALL (cr=29652 pr=29645 pw=0 time=6933175 us)
2353979 2353979 2353979 PARTITION REFERENCE SINGLE PARTITION: 1 1 (cr=29649 pr=29645 pw=0 time=5056105 us cost=8198 size=197734236 card=2353979)
2353979 2353979 2353979 TABLE ACCESS FULL TST_INSTNC PARTITION: 1 1 (cr=29649 pr=29645 pw=0 time=4395761 us cost=8198 size=197734236 card=2353979)
0 0 0 TABLE ACCESS FULL EXTAB_000_001_903 (cr=3 pr=0 pw=0 time=309 us cost=2 size=395 card=1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc cr grant 2-way 1 0.00 0.00
db file sequential read 52831 0.10 32.53
gc cr multi block request 716 0.00 0.74
db file scattered read 991 0.07 5.60
gc current grant 2-way 52030 0.00 17.07
enq: TT - contention 2 0.00 0.00
direct path write temp 375 0.08 1.79
direct path read temp 893 0.02 0.80
gc current multi block request 1 0.00 0.00
db file parallel read 1 0.00 0.00
gc cr block 2-way 3 0.00 0.00
gc current grant congested 801 0.00 0.27
latch: object queue header operation 1 0.00 0.00
log file switch completion 2 0.09 0.13
gc current block 2-way 71 0.00 0.03

April 27, 2016 - 4:18 am UTC

Just an addenda - things get better in 12c.

https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm

Cheers,
Connor

Exchange partition across tables in different tablespace

Venki, January 17, 2017 - 5:54 am UTC

If I have a table, table1 in tablespace tbs1, which is partitioned and we exchange the partition to another table, table 2 in tablespace tbs2 - will data movement happen from tbs1 to tbs2? or will just pointers just change leaving data in tbs1 itself.

January 17, 2017 - 11:40 am UTC

Yes, you can exchange across tablespaces. This will just change the pointers. So in effect this flips the location of the table and partition involved:

create tablespace tbsp datafile 'tbsp.dbf' size 10M;
create table t (
  x int,
  y int
) tablespace tbsp;


create table tpart (
  x int,
  y int
) partition by range (x) (
  partition p0 values less than (10)
);

insert into t values (1, 2);
insert into tpart values (1, 2);
commit;

select segment_name, tablespace_name, bytes, partition_name
from   user_segments
where  segment_name in ('T', 'TPART');

SEGMENT_NAME  TABLESPACE_NAME  BYTES   PARTITION_NAME  
T             TBSP             65,536                  
TPART         SYSTEM           16,384  P0  

select object_name, object_id, data_object_id from user_objects
where  object_name in  ('T', 'TPART');

OBJECT_NAME  OBJECT_ID  DATA_OBJECT_ID  
T            82,991     82,991          
TPART        82,992                     
TPART        82,993     82,993   

alter table tpart exchange partition p0 with table t;

select segment_name, tablespace_name, bytes, partition_name
from   user_segments
where  segment_name in ('T', 'TPART');

SEGMENT_NAME  TABLESPACE_NAME  BYTES   PARTITION_NAME  
TPART         TBSP             65,536  P0              
T             SYSTEM           16,384   

select object_name, object_id, data_object_id from user_objects
where  object_name in  ('T', 'TPART');

OBJECT_NAME  OBJECT_ID  DATA_OBJECT_ID  
TPART        82,993     82,991          
TPART        82,992                     
T            82,991     82,993

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Test of modified DDL

Final Post

Trace of Resource Consumption

Exchange partition across tables in different tablespace

More to Explore

VLDB

VLDB

EXCHANGE partition and indexes

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Test of modified DDL

Final Post

Trace of Resource Consumption

Exchange partition across tables in different tablespace

More to Explore

VLDB

VLDB