Skip to Main Content
  • Questions
  • Best Practice to convert 400 million rows table with 3 TB of CLOB to partition table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database