Hello,

I have two tables:
1st - the empty table t1 (partitioned by date) on the server s1,
2nd - the source table t2 (partitioned by date) on the server s2.

Both tables t1 and t2 has the same structure.

I want to copy t1 from t2 partition by partition using PL/SQL.

First I used the following code :

declare
..
begin
..
  insert into /*+ append */ t1
  select *
  from t2@link_to_t2 
  where
  DATE_TIME>=day_to_copy --day_to_copy - variable using to specify particular aprtition
  and DATE_TIME<day_to_copy+1;
  commit;
..
end;

It worked, but the size of t1 partitons were approximately twice bigger than the same partitions of t2.

When I used the execute immediate:

declare
query varchar2(500);
..
begin
..
  query:='insert /*+ append*/ t1 select * from t2@link_to_t2 where 
  DATE_TIME>= :v_date and DATE_TIME< :v_date2';
  EXECUTE IMMEDIATE query using day_to_copy, day_to_copy+1;
  commit;
..
end;

the sizes of the partitions of t1 and t2 were the same (and also this method was faster).

What is the cause of such big increase of tablespace size using these two ways of copying table?

Thanks,
Greg


-------------------------------------------------
--- Extra information
-------------------------------------------------
The copied table was bigger by the size of the table (original 3,94GB vs copied 7,42GB). The size was checked by comparing the size of particular partitions of the user_segments.
The number of the rows was the same.

The definition of both tables are the same - based on dmbs_metadata.get_ddl:

  CREATE TABLE "S1"."T1" 
   (  "COL_1" VARCHAR2(100), 
  "COL_2" DATE, 
  "COL_3" VARCHAR2(4000), 
  "COL_4" VARCHAR2(100), 
  "COL_5" VARCHAR2(100), 
  "COL_6" VARCHAR2(100), 
  "COL_7" NUMBER, 
  "COL_8" VARCHAR2(1000), 
  "COL_9" VARCHAR2(1000), 
  "COL_10" NUMBER, 
  "COL_11" NUMBER, 
  "COL_12" VARCHAR2(1000), 
  "COL_13" VARCHAR2(1000), 
  "COL_14" VARCHAR2(1000), 
  "COL_15" VARCHAR2(1000), 
  "COL_16" VARCHAR2(1000), 
  "COL_17" VARCHAR2(1000), 
  "COL_18" VARCHAR2(1000), 
  "COL_19" VARCHAR2(1000), 
  "COL_20" VARCHAR2(1000), 
  "COL_21" VARCHAR2(1000), 
  "COL_22" VARCHAR2(1000), 
  "COL_23" NUMBER, 
  "COL_24" VARCHAR2(100), 
  "COL_25" VARCHAR2(10), 
  "COL_26" NUMBER, 
  "COL_27" NUMBER, 
  "COL_28" NUMBER, 
  "COL_29" NUMBER, 
  "COL_30" NUMBER, 
  "COL_31" VARCHAR2(10), 
  "COL_32" NUMBER, 
  "COL_33" VARCHAR2(100), 
  "COL_34" VARCHAR2(100), 
  "COL_35" VARCHAR2(100), 
  "COL_36" VARCHAR2(100), 
  "COL_37" VARCHAR2(1000), 
  "COL_38" VARCHAR2(4000), 
  "COL_39" VARCHAR2(100), 
  "COL_40" VARCHAR2(100), 
  "COL_41" VARCHAR2(100), 
  "COL_42" VARCHAR2(20), 
  "COL_43" VARCHAR2(100), 
  "COL_44" VARCHAR2(4000), 
  "COL_45" VARCHAR2(100), 
  "COL_46" VARCHAR2(4000), 
  "COL_47" NUMBER, 
  "COL_48" VARCHAR2(4000), 
  "COL_49" VARCHAR2(400)
   ) PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC  NOLOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "S1_RAW" 
  PARTITION BY RANGE ("COL_2") 
 (
 PARTITION "P20161030"  VALUES LESS THAN (TO_DATE(' 2016-10-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "S1_RAW" , 
 PARTITION "P20161031"  VALUES LESS THAN (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "S1_RAW" , 
 PARTITION "P20161101"  VALUES LESS THAN (TO_DATE(' 2016-11-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "S1_RAW" 
) 

there are also defined the same indexes for these both tables:

  CREATE INDEX "S1"."T1_IDX" ON "S1"."T1" ("COL_7") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
 (
 PARTITION "P20161030" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 
  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)
  TABLESPACE "S1_RAW" , 
 PARTITION "P20161031" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 
  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)
  TABLESPACE "S1_RAW" , 
 PARTITION "P20161101" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 
  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)
  TABLESPACE "S1_RAW" 
) 

When I war trying to repeat this case I think I found the cause of the problem.
The correct copying (the size (bytes) of the original and the copy is comparable).

CASE 1 [correct]:

declare
cursor kk is 
select trunc(sysdate)-level day_to_copy
from dual where level>90 connect by level<=92
order by 1;
begin
  for i in kk loop
  insert into log_copy values(sysdate,'Start: '||to_char(i.day_to_copy,'yyyy-mm-dd'));
  commit;
    insert /*+ append */ into t1
    select *
    from t2@link_to_t2
    where
    col_2>=i.day_to_copy
    and col_2<i.day_to_copy+1;
    commit;
  insert into log_copy values(sysdate,'Stop: '||to_char(i.day_to_copy,'yyyy-mm-dd'));
  commit;
  end loop;
end;

CASE 2 [correct]:

declare
cursor kk is 
select trunc(sysdate)-level day_to_copy
from dual where level>90 connect by level<=92
order by 1;
querry varchar2(700);
begin
  for i in kk loop
  insert into log_copy values(sysdate,'Start: '||to_char(i.day_to_copy,'yyyy-mm-dd'));
  commit;
  querry:='
    insert /*+ append */ into t1
    select *
    from t2@link_to_t2
    where
    col_2>= :var_date_1
    and col_2< :var_date_2';
    EXECUTE IMMEDIATE querry using i.day_to_copy, i.day_to_copy+1;
    commit;
  insert into log_copy values(sysdate,'Stop: '||to_char(i.day_to_copy,'yyyy-mm-dd'));
  commit;
  end loop;
end;

CASE 3 [incorrect]:

declare
cursor kk is 
select trunc(sysdate)-level day_to_copy
from dual where level>90 connect by level<=92
order by 1;
begin
  for i in kk loop
  insert into log_copy values(sysdate,'Start: '||to_char(i.day_to_copy,'yyyy-mm-dd'));
  commit;
    insert into /*+ append */ t1
    select *
    from t2@link_to_t2
    where
    col_2>=i.day_to_copy
    and col_2<i.day_to_copy+1;
    commit;
  insert into log_copy values(sysdate,'Stop: '||to_char(i.day_to_copy,'yyyy-mm-dd'));
  commit;
  end loop;
end;

As I mentioned above the problem was placing the hint - comparing CASE 1

    insert /*+ append */ into t1

and CASE 3:

    insert into /*+ append */ t1

Can you explain what is the reason of such size increase using the methods of the CASE 1 and 3? What will be the result if the 'append' hint is omitted?