Caveat
Kevin Kirkpatrick, January 21, 2016 - 5:23 pm UTC
Caution, the solution makes two assumptions:
1) the partition P1 already exists on PARTITIONED_TABLE
2) the partition is empty.
#1 isn't a big deal... the only fallout will be script failing with error. You'll typically run into this if PARTITIONED_TABLE uses interval partitioning (so you don't already have script to add new partitions manually). There's probably a cleaner way, but I use this "dirty trick" to get around this:
After CTAS, but before EXCHANGE PARTION, do this:
INSERT INTO PARTITIONED_TABLE SELECT * FROM TMP WHERE ROWNUM <= 1;
ROLLBACK;
#2 is a potentially catastrophic oversight. If script is run and #2 is untrue, you can probably expect some frantic phone calls in the near future (and, if you're the tidy-ship type who doesn't let TMP tables clutter up the catalog, your DBA is - hopefully - about to become your new favorite person).
So if you're even a little unsure about #2 (note - there's really no problem using this approach even if you KNOW you're only loading to empty partitions)...
IF existing data in non-empty partition won't be significantly larger than data being loaded from STAGING_TABLE, a slight modification of the CTAS in original solution should do the trick:
create table TMP as
select * from staging_table
where <all the rows for partition 1>
union all
select * from PARTITIONED_TABLE
where <all the rows for partition 1>;
If existing data of partition is significantly larger than data to be loaded (e.g. loading 100 million rows into a partition which already has 10 billion rows), this probably won't fly. Best bet would be to figure out a sub-partitioning scheme (and apply EXCHANGE PARTITION method at SUBPARTITION level). Failing that, you may be able to run a script, off-hours, that sets index partition to unusable, append-loads data to the partition, then rebuilds the unusable index partition (off-hours because query performance would be impacted while index partition is unusable).
January 22, 2016 - 1:18 am UTC
good input