-
Non-partition table to partition
Hi,
Could you please share your views to convert non-partitioned table to partitioned which is 600GB size?
Option 1: using data pump
Option 2: pre create partition table and do insert with append hint
Option 3: use dbms_redefinition package
As the table size is huge, I prefer option 1.
Thanks in advance.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Originally Posted by vnktummala
Could you please share your views to convert non-partitioned table to partitioned which is 600GB size?
Option 1: using data pump
Option 2: pre create partition table and do insert with append hint
Option 3: use dbms_redefinition package
We recently used option #2 for a two billion rows table; we chose #2 because less physical I/O than option #1 and also for allowing more "control" over the whole process.
1- pre create partitioned table -in our case date range per YYYYMM, one tablespace per "year"
2- start multiple insert streams -in our case one job per year so each job would take care of loading twelve YYYYMM partitions using append hint.
3- build indexes -in our case all of them locally partitioned.
4- gather fresh performance stats.
It worked like a charm.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks for your input Paul.
My case also similar. I have 1.6 billion rows in that table. New partition key also same as your partition key format. But I don't have any Index on the existing table.
choosing option#2 is costlier for me because every insert stream I start has to do FTS on the existing table. So, I still believe option#1 is feasible for me.
Appreciate your input.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Originally Posted by vnktummala
choosing option#2 is costlier for me because every insert stream I start has to do FTS on the existing table.
I do agree all streams will be doing a FTS on source table but since the idea is to run all of them at the same time only one of them will be doing actual physical reads, all others will be getting already cached blocks
Last edited by PAVB; 05-12-2011 at 01:39 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by vnktummala
Thanks for your input Paul.
My case also similar. I have 1.6 billion rows in that table. New partition key also same as your partition key format. But I don't have any Index on the existing table.
choosing option#2 is costlier for me because every insert stream I start has to do FTS on the existing table. So, I still believe option#1 is feasible for me.
Appreciate your input.
Thanks,
insert into new_partitioned_table select * from old_table;
Why can't you parallelize that operation (or a CTAS statement)?
-
Thanks Paul. I have done this using data pump. export took 2 hours for 1.6 billion rows and import took 5 hours. However, I do have another very huge table with 9 billion rows for which I need to change the partition column. I will go with your suggestion for that table.
Thanks Stecal.
insert into new_partitioned_table select * from old_table;
It's a big table with 1.6 billion rows. So I don't want to take a chance to insert every thing with out any commit. Rather I can use insert with append and start multiple insert streams as Paul suggested.
I don't think so CTAS is applicable here because existing table is non-partitioned and new table is partitioned.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
Nothing is stopping you from CTAS with partitions.
-
Thanks Stecal, I will test this option too.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|