Skip to Main Content
  • Questions
  • Loading huge volume of data to a partition table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bala.

Asked: January 21, 2016 - 4:56 am UTC

Last updated: January 22, 2016 - 1:18 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello,

I have 110 million records in a staging table, which has to be loaded to a partition table. Without having any index in the partition table, I can load the data with in 5 mins with append & nologging hint. I have created two local index with nologging. After that I have loaded the same set of data with same hint, after 4 hours also, not able to load the data. Is there any way I can load the data with index.

and Connor said...

'exchange partition' might be an option here.

Lets say your 110m staging table rows span (say) 3 partitions worth of data in your target table. Then you do:

1) create table TMP as select * from staging_table
where <all the rows for partition 1>

2) index TMP with the same indexes as you have in the partition table

3) alter table PARTITIONED_TABLE exchange partition p1 with table TMP;

Repeat for the other two partitions

An example here: http://www.akadia.com/services/ora_exchange_partition.html

Rating

  (1 rating)

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

Comments

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).

Chris Saxon
January 22, 2016 - 1:18 am UTC

good input