Skip to Main Content
  • Questions
  • Insert Into table_name Select for table with CLOB column is very slow.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Wasim.

Asked: February 01, 2017 - 2:31 am UTC

Last updated: February 03, 2017 - 7:36 pm UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

Hi Sir,

I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity.

rename existing table to _old
create new partition blank table
insert into new blank partition table select * from _old table;

This preocess is running from last 12 hrs and only 20Gb of data is inserterd the table size is 90 Gb with 2 CLOB segments of 900 and 200Gb each.


Need your suggestion how to overcome this time consumption and make the process fast.

Below is the details of table and execution plan.

NOTE:

REFERENCE_INSTANCE -- ORIGINAL NON PARTITION TABLE.

In GB::

EAI_SOAINFRA REFERENCE_INSTANCE TABLE 103.708008
EAI_SOAINFRA SYS_LOB0000075987C00014$$ LOBSEGMENT 241.97522 REFERENCE_INSTANCE
EAI_SOAINFRA SYS_LOB0000075987C00015$$ LOBSEGMENT 924.013855 REFERENCE_INSTANCE



REFERENCE_INSTANCE_TP -- ENPTY PARTITION TABLE CREATED SAME AS REFERENCE_INSTANCE EXCEPT THE NAME DIFFERENCE AND PARTITION DETAILS.


CREATE TABLE "EAI_SOAINFRA"."REFERENCE_INSTANCE_TP"
( "ECID" VARCHAR2(100 BYTE),
"ID" NUMBER(*,0),
"COMPOSITE_INSTANCE_ID" NUMBER(*,0),
"PARENT_ID" VARCHAR2(100 BYTE),
"CONVERSATION_ID" VARCHAR2(100 BYTE),
"COMPOSITE_DN" VARCHAR2(500 BYTE),
"PROTOCOL_CORRELATION_ID" VARCHAR2(100 BYTE),
"REFERENCE_NAME" VARCHAR2(200 BYTE),
"BINDING_TYPE" VARCHAR2(200 BYTE),
"OPERATION_NAME" VARCHAR2(500 BYTE),
"STATE" NUMBER(*,0),
"ADDITIONAL_PROPERTIES" VARCHAR2(4000 BYTE),
"ERROR_CODE" VARCHAR2(100 BYTE),
"ERROR_MESSAGE" CLOB,
"STACK_TRACE" CLOB,
"CPST_PARTITION_DATE" TIMESTAMP (6),
"CREATED_BY" VARCHAR2(100 BYTE),
"CREATED_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"UPDATED_BY" VARCHAR2(100 BYTE),
"UPDATED_TIME" TIMESTAMP (6)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 20 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EAI_SOAINFRA"
LOB ("ERROR_MESSAGE") STORE AS BASICFILE (
TABLESPACE "EAI_SOAINFRA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("STACK_TRACE") STORE AS BASICFILE (
TABLESPACE "EAI_SOAINFRA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
PARTITION BY RANGE ("CPST_PARTITION_DATE")
(
PARTITION "AIATBLPART_JANP1_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-05 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP2_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-10 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP3_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-15 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP4_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-20 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP5_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-25 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_JANP6_2017" VALUES LESS THAN (TIMESTAMP' 2017-01-31 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP1_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-05 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP2_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-10 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP3_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-15 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP4_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-20 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP5_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-25 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_FEBP6_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-28 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP1_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-05 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP2_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-10 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP3_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-15 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP4_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-20 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP5_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-25 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MARP6_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-28 00:00:00') TABLESPACE "EAI_SOAINFRA" ,
PARTITION "AIATBLPART_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "EAI_SOAINFRA" )
ENABLE ROW MOVEMENT;

alter session enable parallel dml;
INSERT /*+ PARALLEL(b,22) */ INTO EAI_SOAINFRA.REFERENCE_INSTANCE_TP b SELECT /*+ PARALLEL(a,22) */ * from EAI_SOAINFRA.REFERENCE_INSTANCE a;

COMMIT;


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4zwvtyf1bzdrt, child number 0
-------------------------------------
INSERT /*+ PARALLEL(b,22) */ INTO EAI_SOAINFRA.REFERENCE_INSTANCE_TP b SELECT /*+ PARALLEL(a,22) */ * from EAI_SOAINFRA.REFERENCE_INSTANCE a ;

Plan hash value: 795120472

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 186K(100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 340M| 84G| 186K (1)| 00:37:18 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 340M| 84G| 186K (1)| 00:37:18 | Q1,01 | PCWP | |
| 5 | PX SEND PARTITION (KEY)| :TQ10000 | 340M| 84G| 186K (1)| 00:37:18 | Q1,00 | P->P | PART (KEY) |
| 6 | PX BLOCK ITERATOR | | 340M| 84G| 186K (1)| 00:37:18 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | REFERENCE_INSTANCE | 340M| 84G| 186K (1)| 00:37:18 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access(:Z>=:Z AND :Z<=:Z)

SID USERNAME PROGRAM OSUSER EVENT STATE SQL SQL_ID MINS_ACTIVE
---------- ------------ ------------ ------------ ------------------------- -------------- ------------------------------ ------------- -----------
6 SYS sqlplus@PRE oracle SQL*Net message to client WAITED SHORT select distinct w.sid,s.userna f1wvs9jwvcmv7 0
254 SYSTEM oracle@PREP oracle db file sequential read WAITING INSERT /*+ APPEND PARALLEL(b,2 4zwvtyf1bzdrt 864
258 SYSTEM oracle@PREP oracle db file sequential read WAITING INSERT /*+ APPEND PARALLEL(b,2 4zwvtyf1bzdrt 864
2756 SYSTEM oracle@PREP oracle db file sequential read WAITING INSERT /*+ APPEND PARALLEL(b,2 4zwvtyf1bzdrt 864



Request you to please suggest me what is wroing with this approach and what should i do for making it fast.

Regards
Wasim

and Chris said...

So you're transferring over a terabyte of data? That's going to take a while whatever you do...

And reading and writing clobs does a lot more work than "standard" data types. For example, a "insert ... select ..." of just 10,000 rows takes over half a minute on my system:

SQL> set timing off
SQL> create table t (
  2    x int,
  3    y clob
  4  );

Table created.

SQL>
SQL> create table tpart (
  2    x int,
  3    y clob
  4  );

Table created.

SQL>
SQL> declare
  2    v varchar2(32000) := lpad('xxxxxxxxxxx', 32000, 'x');
  3    ctemp clob;
  4  begin
  5
  6    dbms_lob.createtemporary(ctemp,true);
  7    ctemp := v;
  8
  9    for i in 1 .. 20 loop
 10      dbms_lob.writeappend(ctemp,length(v),v);
 11    end loop;
 12
 13    insert into T
 14      select rownum, ctemp from dual connect by level <= 10000;
 15    commit;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
     10000

SQL>
SQL> set timing on
SQL> insert into tpart
  2    select * from t;

10000 rows created.

Elapsed: 00:00:43.56


Compared to ~5s for similar varchar data:

SQL> create table tv (
  2    x int,
  3    y varchar2(4000)
  4  );

Table created.

Elapsed: 00:00:00.32
SQL>
SQL> create table tpartv (
  2    x int,
  3    y varchar2(4000)
  4  );

Table created.

Elapsed: 00:00:00.32
SQL>
SQL> insert into tv
  2     select rownum, lpad('x', 4000, 'x')
  3    from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:04.57
SQL> commit;

Commit complete.

Elapsed: 00:00:00.54
SQL>
SQL> insert into tpartv
  2    select * from tv;

10000 rows created.

Elapsed: 00:00:05.20


Fortunately there's a way you can avoid actually copying the data.

Create your partitioned table with one partition. Then swap the tables over with an exchange partition! This is a metadata operation rather than a physical transfer, so will be much quicker:

SQL> drop table tpart purge;

Table dropped.

Elapsed: 00:00:00.47
SQL>
SQL> create table tpart (
  2    x int,
  3    y clob
  4  ) partition by range (x) (
  5    partition pmax values less than (maxvalue)
  6  );

Table created.

Elapsed: 00:00:00.36
SQL>
SQL> alter table tpart exchange partition pmax with table t;

Table altered.

Elapsed: 00:00:00.39
SQL>
SQL> select count(*) from tpart;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.47
SQL> select count(*) from t;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.80


Once this is done, split your partitions to get the arrangement you want.

Rating

  (6 ratings)

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

Comments

Exaple if any spf split partition

Wasim Shaikh, February 01, 2017 - 2:32 pm UTC

Thank u so much sir, i can follow the approached privided. If you can elaborate with some example.

Regards
Wasim

Split partition is too slow

Wasim Shaikh, February 02, 2017 - 5:58 am UTC

Hi Sir,

I am following your step, below is the details of it. but split partition is very slow.

11:53:52 SQL> ---CREATE BLANK PARTITION TABLE WITH ONLY ONE PARTITION
11:54:07 SQL>
11:54:08 SQL> CREATE TABLE "AIA_SOAINFRA"."REFERENCE_INSTANCE_PT"
11:54:09 2 ( "ECID" VARCHAR2(100 BYTE),
"ID" NUMBER(*,0),
11:54:09 3 11:54:09 4 "COMPOSITE_INSTANCE_ID" NUMBER(*,0),
11:54:09 5 "PARENT_ID" VARCHAR2(100 BYTE),
11:54:09 6 "CONVERSATION_ID" VARCHAR2(100 BYTE),
11:54:09 7 "COMPOSITE_DN" VARCHAR2(500 BYTE),
11:54:09 8 "PROTOCOL_CORRELATION_ID" VARCHAR2(100 BYTE),
11:54:09 9 "REFERENCE_NAME" VARCHAR2(200 BYTE),
11:54:09 10 "BINDING_TYPE" VARCHAR2(200 BYTE),
11:54:09 11 "OPERATION_NAME" VARCHAR2(500 BYTE),
11:54:09 12 "STATE" NUMBER(*,0),
11:54:09 13 "ADDITIONAL_PROPERTIES" VARCHAR2(4000 BYTE),
11:54:09 14 "ERROR_CODE" VARCHAR2(100 BYTE),
11:54:09 15 "ERROR_MESSAGE" CLOB,
11:54:09 16 "STACK_TRACE" CLOB,
"CPST_PARTITION_DATE" TIMESTAMP (6),
11:54:09 17 "CREATED_BY" VARCHAR2(100 BYTE),
"CREATED_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"UPDATED_BY" VARCHAR2(100 BYTE),
"UPDATED_TIME" TIMESTAMP (6)
11:54:09 18 ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 20 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
11:54:09 19 11:54:09 20 TABLESPACE "AIA_SOAINFRA"
LOB ("ERROR_MESSAGE") STORE AS BASICFILE (
11:54:09 21 11:54:09 22 11:54:09 23 TABLESPACE "AIA_SOAINFRA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
11:54:09 24 LOB ("STACK_TRACE") STORE AS BASICFILE (
TABLESPACE "AIA_SOAINFRA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
PARTITION BY RANGE ("CREATED_TIME")
11:54:09 25 11:54:09 26 (
PARTITION "AIATBLPART_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "AIA_SOAINFRA"
);11:54:09 27 11:54:09 28 11:54:09 29 11:54:09 30 11:54:09 31 11:54:09 32 11:54:09 33 11:54:09 34 11:54:09 35

Table created.

Elapsed: 00:00:00.70
11:54:11 SQL>
12:00:35 SQL>
12:00:36 SQL> alter table AIA_SOAINFRA.REFERENCE_INSTANCE_PT exchange partition AIATBLPART_MAX with table AIA_SOAINFRA.REFERENCE_INSTANCE;

Table altered.

Elapsed: 00:07:05.15
12:07:52 SQL>
12:07:58 SQL> @ind
Enter value for tb: REFERENCE_INSTANCE
Enter value for owner: AIA_SOAINFRA
old 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('&TB') and index_owner=upper('&OWNER') order by INDEX_NAME,COLUMN_POSITION
new 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('REFERENCE_INSTANCE') and index_owner=upper('AIA_SOAINFRA') order by INDEX_NAME,COLUMN_POSITION

INDEX_OWNER TABLE_NAME COLUMN_NAME INDEX_NAME COLUMN_POSITION
------------------------------ ------------------------------ ------------------------- ------------------------------ ---------------
AIA_SOAINFRA REFERENCE_INSTANCE COMPOSITE_DN REFERENCE_INSTANCE_CDN_STATE 1
AIA_SOAINFRA REFERENCE_INSTANCE STATE REFERENCE_INSTANCE_CDN_STATE 2
AIA_SOAINFRA REFERENCE_INSTANCE PROTOCOL_CORRELATION_ID REFERENCE_INSTANCE_CO_ID 1
AIA_SOAINFRA REFERENCE_INSTANCE ECID REFERENCE_INSTANCE_ECID 1
AIA_SOAINFRA REFERENCE_INSTANCE ID REFERENCE_INSTANCE_ID 1
AIA_SOAINFRA REFERENCE_INSTANCE STATE REFERENCE_INSTANCE_STATE 1
AIA_SOAINFRA REFERENCE_INSTANCE SYS_NC00021$ REFERENCE_INSTANCE_TIME_CDN 1
AIA_SOAINFRA REFERENCE_INSTANCE COMPOSITE_DN REFERENCE_INSTANCE_TIME_CDN 2
AIA_SOAINFRA REFERENCE_INSTANCE STATE REFERENCE_INSTANCE_TIME_CDN 3

9 rows selected.

Elapsed: 00:00:00.08
12:10:23 SQL> @ind
Enter value for tb: REFERENCE_INSTANCE_PT
Enter value for owner: AIA_SOAINFRA
old 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('&TB') and index_owner=upper('&OWNER') order by INDEX_NAME,COLUMN_POSITION
new 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('REFERENCE_INSTANCE_PT') and index_owner=upper('AIA_SOAINFRA') order by INDEX_NAME,COLUMN_POSITION

no rows selected

Elapsed: 00:00:00.05
12:10:46 SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='REFERENCE_INSTANCE_PT';

TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ---------------------- ----------
AIA_SOAINFRA REFERENCE_INSTANCE_PT AIATBLPART_MAX MAXVALUE 80712218

Elapsed: 00:00:00.10
12:12:07 SQL> ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE RENAME TO REFERENCE_INSTANCE_NP;

Table altered.

Elapsed: 00:00:01.18
12:12:27 SQL> @ind
Enter value for tb: REFERENCE_INSTANCE_NP
Enter value for owner: AIA_SOAINFRA
old 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('&TB') and index_owner=upper('&OWNER') order by INDEX_NAME,COLUMN_POSITION
new 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('REFERENCE_INSTANCE_NP') and index_owner=upper('AIA_SOAINFRA') order by INDEX_NAME,COLUMN_POSITION

INDEX_OWNER TABLE_NAME COLUMN_NAME INDEX_NAME COLUMN_POSITION
------------------------------ ------------------------------ ------------------------- ------------------------------ ---------------
AIA_SOAINFRA REFERENCE_INSTANCE_NP COMPOSITE_DN REFERENCE_INSTANCE_CDN_STATE 1
AIA_SOAINFRA REFERENCE_INSTANCE_NP STATE REFERENCE_INSTANCE_CDN_STATE 2
AIA_SOAINFRA REFERENCE_INSTANCE_NP PROTOCOL_CORRELATION_ID REFERENCE_INSTANCE_CO_ID 1
AIA_SOAINFRA REFERENCE_INSTANCE_NP ECID REFERENCE_INSTANCE_ECID 1
AIA_SOAINFRA REFERENCE_INSTANCE_NP ID REFERENCE_INSTANCE_ID 1
AIA_SOAINFRA REFERENCE_INSTANCE_NP STATE REFERENCE_INSTANCE_STATE 1
AIA_SOAINFRA REFERENCE_INSTANCE_NP SYS_NC00021$ REFERENCE_INSTANCE_TIME_CDN 1
AIA_SOAINFRA REFERENCE_INSTANCE_NP COMPOSITE_DN REFERENCE_INSTANCE_TIME_CDN 2
AIA_SOAINFRA REFERENCE_INSTANCE_NP STATE REFERENCE_INSTANCE_TIME_CDN 3

9 rows selected.

Elapsed: 00:00:00.05
12:12:41 SQL> ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE_PT RENAME TO REFERENCE_INSTANCE;

Table altered.

Elapsed: 00:00:00.07
12:12:56 SQL> @ind
Enter value for tb: REFERENCE_INSTANCE
Enter value for owner: AIA_SOAINFRA
old 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('&TB') and index_owner=upper('&OWNER') order by INDEX_NAME,COLUMN_POSITION
new 1: select INDEX_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME,COLUMN_POSITION from dba_ind_columns where TABLE_NAME=upper('REFERENCE_INSTANCE') and index_owner=upper('AIA_SOAINFRA') order by INDEX_NAME,COLUMN_POSITION

no rows selected

Elapsed: 00:00:00.02
12:13:05 SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,HIGH_VALUE,NUM_ROWS FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='REFERENCE_INSTANCE';

TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ---------------------- ----------
AIA_SOAINFRA REFERENCE_INSTANCE AIATBLPART_MAX MAXVALUE 80712218

Elapsed: 00:00:00.09
12:13:14 SQL> -- NOW REFRENCE_INSTANCE IS A PARTITION TABLE WITH ONLY 1 PARTITION
12:30:43 SQL>
12:30:43 SQL> ALTER SESSION enable parallel ddl;

Session altered.

Elapsed: 00:00:00.00
12:33:41 SQL> ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE SPLIT PARTITION AIATBLPART_MAX
AT (TO_DATE('01-01-2016','mm-dd-yyyy')) INTO (PARTITION AIATBLPART_DEC_2015,PARTITION AIATBLPART_MAX);
12:36:58 2
*********+++++RUNNING FROM LAST 1 HOUR*********+++++

Session Details are below.
---------------------------

SID USERNAME PROGRAM OSUSER EVENT STATE SQL SQL_ID MINS_ACTIVE
---- ------------ ------------ ------------ ------------------------- -------------- ------------------------------ ------------- -----------
#### SYS sqlplus@PRE oracle SQL*Net message to client WAITED SHORT select distinct w.sid,s.userna f1wvs9jwvcmv7 0
14 SYS sqlplus@PRE oracle db file sequential read WAITING ALTER TABLE AIA_SOAINFRA.REFE 1zpx564t3u5sb 63


Enter value for sql: 1zpx564t3u5sb
old 1: select * from table(dbms_xplan.display_cursor('&sql'))
new 1: select * from table(dbms_xplan.display_cursor('1zpx564t3u5sb'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1zpx564t3u5sb, child number 0
-------------------------------------
ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE SPLIT PARTITION
AIATBLPART_MAX AT (TO_DATE('01-01-2016','mm-dd-yyyy')) INTO (PARTITION
AIATBLPART_DEC_2015,PARTITION AIATBLPART_MAX)

Plan hash value: 108406522

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 1018K(100)| | | |
| 1 | LOAD AS SELECT | | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 80M| 24G| 1018K (1)| 03:23:46 | 1 | 1 |
| 3 | TABLE ACCESS FULL | REFERENCE_INSTANCE | 80M| 24G| 1018K (1)| 03:23:46 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------


17 rows selected.

Elapsed: 00:00:00.06


Request you to please suggest any change to speedup this process.

Regards
Wasim
Chris Saxon
February 02, 2017 - 9:50 am UTC

You can run split partitions in parallel:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel003.htm#BEICHHII

So this is worth investigating. And can you drop the indexes while doing the split and re-creating them afterwards?

Finally: do you actually need to split the existing data? Can you get away with just creating partitions for new data?

Wasim Shaikh, February 02, 2017 - 10:27 am UTC

HI Sir,

I did the process in parallel already and its still running from last 210 Mins.

Below is the approch i used for parallel split.

set time on
set timing on
set serveroutput on
set echo on
ALTER SESSION FORCE PARALLEL DDL PARALLEL 20;
ALTER SESSION FORCE PARALLEL DML PARALLEL 20;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 20;
ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE SPLIT PARTITION AIATBLPART_MAX AT (TO_DATE('01-01-2015','mm-dd-yyyy')) INTO (PARTITION AIATBLPART_DEC_2014,PARTITION AIATBLPART_MAX);
ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE SPLIT PARTITION AIATBLPART_MAX AT (TO_DATE('01-01-2016','mm-dd-yyyy')) INTO (PARTITION AIATBLPART_DEC_2015,PARTITION AIATBLPART_MAX);



Session Information:

---------- ------------ ------------ ------------ ------------------------- -------------- ------------------------------ ------------- -----------
3512 SYSTEM sqlplus@PRE oracle db file sequential read WAITING ALTER TABLE AIA_SOAINFRA.REFE cm5zjnbc56b56 201

SQL> @cur
Enter value for sql: cm5zjnbc56b56

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cm5zjnbc56b56, child number 0
-------------------------------------
ALTER TABLE AIA_SOAINFRA.REFERENCE_INSTANCE SPLIT PARTITION
AIATBLPART_MAX AT (TO_DATE('01-01-2015','mm-dd-yyyy')) INTO (PARTITION
AIATBLPART_DEC_2014,PARTITION AIATBLPART_MAX)

Plan hash value: 2680463345

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 56485 (100)| | | | | | |
| 1 | LOAD AS SELECT | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 80M| 24G| 56485 (1)| 00:11:18 | | | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 80M| 24G| 56485 (1)| 00:11:18 | 1 | 1 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | REFERENCE_INSTANCE | 80M| 24G| 56485 (1)| 00:11:18 | 1 | 1 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)


24 rows selected.

Yes we need the historical data in a single partition but last 6 months into its individual partitions.

SQL>

Regards
Wasim
Chris Saxon
February 03, 2017 - 4:15 pm UTC

Have you tried removing indexes?

Humm?

Gh.., February 02, 2017 - 4:46 pm UTC

After all why are you complicating things?
Use interval partitioning and let oracle do the work.
You can for instance create the partions by invoking a dummy inserts and then getting the partition FOR xx and then exchange.

Chris Saxon
February 03, 2017 - 4:16 pm UTC

How would that help? The point of having a single partition is that you can switch the tables over in one exchange.

Did'nt understand

Wasim Shaikh, February 03, 2017 - 1:49 am UTC

hi Sir,

Please elaborate little more.

Regards
Wasim
Chris Saxon
February 03, 2017 - 4:17 pm UTC

You mean with regards to the previous comment? I'm not sure how it helps either!

not gonna work

Wasim Shaikh, February 03, 2017 - 4:50 pm UTC

Exactly,

I have not only left with one option i ave to keep the single partition till 6 month and drop after that, i will not create my maxdate partition as the date on which i will create partition as i am thinking to keep 15days partition to it will be as below.

PARTITION BY RANGE ("CPST_PARTITION_DATE")
(
PARTITION "AIATBLPART_FEB_P1_2017" VALUES LESS THAN (TIMESTAMP' 2017-02-16 00:00:00') TABLESPACE "AIA_SOAINFRA" ,
PARTITION "AIATBLPART_FEB_P2_2017" VALUES LESS THAN (TIMESTAMP' 2017-03-01 00:00:00') TABLESPACE "AIA_SOAINFRA" ,
PARTITION "AIATBLPART_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "AIA_SOAINFRA"
);

Since I am planning to do it on 15th so all my data will be exchnage in AIATBLPART_FEB_P1_2017 this will be my historical partition and AIATBLPART_FEB_P2_2017 will be my new partition for new data which will be maintain.

I cannot use interval partition because of AIA product limitation for archiving and purging.

Anything you would like to highlight?

Regards
Wasim
Connor McDonald
February 03, 2017 - 7:36 pm UTC

Have you considered DBMS_REDEFINITION ?

This does not speed up the process, but keeps your source table online and available whilst this restructure takes place.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.