Thanks for the question, Devinder Singh.
Asked: February 07, 2018 - 3:27 pm UTC
Last updated: February 08, 2018 - 11:10 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi,
I am converting a non-partitioned table to Partitioned(range or Ref). For test scenario, I have a table with 50 GB data (300 million rows) and 100 GB CLOB column data. I tested below options and timings are not as per my satisfaction level.
1. Create Table As Select -- Took 8 hrs
2. DBMS_REDIFINITION -- Took 6 hrs
3. Create Table As Select without CLOB column -- 15 min
4. Create Table as Select with Empty CLOB (hardcoded) -- 2 hrs 15 min
5. Alter table swap partition NoValidate -- Less than a minute, however data sits in wrong partition.
6. Export/Import -- I lost timings
7. Just delete -- 100 million in 2 hrs.
For 100GB CLOB, if it takes 6 hrs, then 3 TB will take considerable amount of time and online and batch cannot be stopped for this activity.
My expectation is that going forward, I will able to maintain table growth. However if I need to spend sometime for one time activity I have only 8 hrs of window to perform one task. I can split into multiple tasks if needed, but application should able to work after that window. Basically chunking of task should not impact Online/Batch if I am out of window.
Thanks
Singh
and Chris said...
Well, if you're looking for the
best way to do this:
- Upgrade to 12.2
- Use the new alter table ... modify partition syntax!
create table t as
select level x, 'stuff' y from dual
connect by level <= 1000;
select count(*) from user_tab_partitions
where table_name = 'T';
COUNT(*)
0
alter table t modify
partition by hash (x) partitions 2 online ;
select count(*) from user_tab_partitions
where table_name = 'T';
COUNT(*)
2
It's an online operation. And you can specify how you want your indexes partitioned in the process.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877__MODIFY_TO_PARTITIONED-297160C6 Assuming you can't do this in the short term...
Have you tried parallelizing the CTAS operations? What difference does this make?
Are you able to split the CLOBs into a separate table? If not, why are you testing CTAS excluding these? You'll have to load them into the table at some point!
Is this answer out of date? If it is, please let us know via a Comment