Thanks for the question, M.
Asked: July 19, 2017 - 9:50 am UTC
Last updated: July 19, 2017 - 10:24 am UTC
Version: 10G
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
we have a table that is daily partitioned based on activity date. On a daily basis, we do a bulk insert for todays (trunc(sysdate)) partition from previous days partition trunc(sysdate-1) with /*+ append*/ hint ( the value for partitionkey column alone is modified using +1) .
Today, we have data close to 20 million records that gets copied in 15-20 mins. considering the growth in data volume, I believe there should be a better way to do this instead of doing insert select.
I did went through partition exchange which I understand can be done only form a non-partitioned table to a single partition. Could you please suggest if there is any better way to do this?
and Chris said...
Well, you could try copying the data into a new table using create table as select. Then partition exchange the new table with your existing one. e.g. in pseudocode:
drop table temp_tab;
create table temp_tab as
select trunc(sysdate), ...
from tab partition (yesterday_partition);
alter table tab exchange partition today_partition with temp_tab;
But to really help we need to understand what your insert is doing. Find this out, trace your insert with waits enabled. Then parse the trace file using tkprof. You can find instructions on how to do this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof If you still need help after doing this, please post your formatted trace file results.