CTAS create table as select

Index Hint is best solution

----------------------------------CTAS with ORDER BY

create table transactions14 as select * from transactions;

50 SEC

create table transactions15 as select * from transactions ORDER BY FIRMNUMBER,TRANSACTION,SUBTRANS;

90 SEC

--------------------------------------------Parallel CTAS

create table transactions16 parallel (degree 2) as select * from transactions ORDER BY FIRMNUMBER,TRANSACTION,SUBTRANS;

120 SEC

create table transactions17 parallel (degree 2) as select * from transactions;

40 SEC

create table transactions18 parallel (degree 4) as select * from transactions;

50 SEC

create table transactions20 parallel (degree 8) as select * from transactions;

55 SEC

------------------------------------CTAS using INDEX hint---

SELECT * FROM dba_ind_columns WHERE table_name='TRANSACTIONS';

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions;

8 sec

create table transactions23 as select /*+ index(FIRMNUMBER) */ * from transactions;

8 sec

----------------------CTAS WITH PRIMARY KEY

create table transactions24 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions;

ALTER TABLE transactions24 ADD constraint pk_SAUDA23 PRIMARY KEY(FIRMNUMBER,TRANSACTION,SUBTRANS)

-----------------------------------------------------------------------

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions where 1=2;

insert into transactions22 (select * from transactions);

30 sec

insert into transactions22 (select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions);

30sec

insert /*+ parallel(transactions22,2) */ into transactions22 (select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions);

60sec

-----------------------------------------------------------------------

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions where 1=2;

CREATE UNIQUE INDEX "LDBO"."PK_SAUDA1" ON "LDBO"."TRANSACTIONS22" ("FIRMNUMBER", "TRANSACTION", "SUBTRANS") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 1610612736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;

analyze table transactions22 compute STATISTICS;

analyze index PK_SAUDA1 compute STATISTICS;

---------------------------------------------no append

insert into dest select * from source1;

189SEC

---------------------------------------------append

insert /*+ append */ into dest select * from source1;

----------------------------------------CTAS, no parallel--------------

insert /*+ append */ into dest select * from source1;

create table dest as select * from source1;

----------------------------------------CTAS, parallel--------------

alter session force parallel ddl parallel 3;

alter session force parallel query parallel 3;

create table transactions22 as select * from transactions;

40SEC

----------------------------------------CTAS, parallel WITH INDEX--------------

alter session force parallel ddl parallel 3;

alter session force parallel query parallel 3;

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from transactions;

----------------------------------GOOD

CTAS INDEX > CTAS PARALLEL DLL > APPEND

---------------------------------------------------------------------------------------------------

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink where 1=2;

insert into transactions22 (select * from ldbo.transactions@cmldlink);

20 min

---------------------------------------------------

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink where 1=2;

insert into transactions22 (select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink);

2 min 10 sec

-----------------------------------

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink where 1=2;

insert /*+ parallel(transactions22) */ into transactions22 (select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink);

2 min 10 sec

-------------------------------------

create table transactions23 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink;

60 sec

-------------------------------------------------------

create table transactions23 as select /*+ index(TRANSACTIONS PK_SAUDAPRIMARY) */ * from ldbo.transactions@cmldlink;

60 SEC

----------------------

create table transactions23 as select /*+ index(TRANSACTIONS PK_SAUDAPRIMARY,IDXCLIENTSAUDA,IDXCLIENTBRSAUDA) */ * from ldbo.transactions@cmldlink;

10 MIN

--------------------------------------------------------

create table transactions24 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink where 1=2;

insert /*+ append */ into transactions24 select * from transactions23;

40 sec

--------------------------------------------------

alter session force parallel ddl parallel 4;

alter session force parallel query parallel 4;

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink where 1=2;

insert into transactions22 (select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink);

2min

---------------------------------

alter session force parallel ddl parallel 2;

alter session force parallel query parallel 2;

create table transactions22 as select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink where 1=2;

insert into transactions22 (select /*+ index(FIRMNUMBER,TRANSACTION,SUBTRANS) */ * from ldbo.transactions@cmldlink);

2.5 min