Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 11, 2020 - 1:41 pm UTC

Answered by: Connor McDonald - Last updated: May 18, 2020 - 2:52 am UTC

Category: PL/SQL - Version: 12.0

Viewed 1000+ times

You Asked

Hi bro,

Please you should help on this.

My question was :-

I want copy a table from other table with 50 million records, now what is the best method and fastest way to re-create it.

1)PLSQL-BULK COLLECTION WITH LIMIT(It is also taking 2 to 3 days time).
2)Insert All with paralle hints (It throwing some space error after ran 6 to 7 hrs like some database space issue, but after i re-solved it is also taking the same time).
3)Is it good idea to create an index on Target table.
4)Do i need to disable all indexes and constraints on my sourec table ?

Please say the best way if possible, am struggling with all the ways.

and we said...

Not much detail there to go on, but the most probable cause is indexes/constraints on your target table.

For example:

50million rows on a table with no indexes on my laptop...I can do that in ONE minute

SQL> create table src
  2  as select * from dba_Objects;

Table created.

SQL> create table tgt 
  2  as select * from dba_Objects where 1=0;

Table created.

SQL>
SQL> set timing on
SQL>
SQL> insert /*+ APPEND */ into tgt
  2  select s.* from src s,
  3    ( select 1 from dual connect by level <= 50000000/82000 );

49931301 rows created.

Elapsed: 00:01:03.82


1 million rows on the same table...now with 4 indexes on it....

SQL> set timing off
SQL> truncate table tgt;

Table truncated.

SQL>
SQL> create index tgt_ix1 on tgt (owner);

Index created.

SQL> create index tgt_ix2 on tgt (object_name);

Index created.

SQL> create index tgt_ix3 on tgt (object_id);

Index created.

SQL> create index tgt_ix4 on tgt (object_type);

Index created.

SQL>
SQL>
SQL> set timing on
SQL> insert /*+ APPEND */ into tgt
  2  select s.* from src s,
  3    ( select 1 from dual connect by level <= 1000000/82000 );

983868 rows created.

Elapsed: 00:00:14.84


which would extrapolate out to 700 seconds (12mins) for 50 million rows.

Peace out bro.

and you rated our response

  (3 ratings)

Reviews

Doubt

May 12, 2020 - 4:51 pm UTC

Reviewer: Su from India

You mean u r saying, I want disable or drop both constraints n indexes on target?

Should I drop or enable is also fine, if disable after load records I can enable in target.
Connor McDonald

Followup  

May 13, 2020 - 12:30 am UTC

- disable constraints
- set indexes to unusable

Then do your load

- rebuild indexes
- enable constraints

I am not a fan of "dropping" things because then you need to remember the exact DDL to recreate them.

Don't forget triggers

May 13, 2020 - 6:39 am UTC

Reviewer: A reader

.. besides indexes and constraints.

Also, do you have LOBS in the concerned table? Assuming it is being carried out in the same DB.

Cheers!

May 15, 2020 - 12:26 pm UTC

Reviewer: A reader

How to do that disable both constraints and indes on target table ?
Connor McDonald

Followup  

May 18, 2020 - 2:52 am UTC

alter index IX unusable;
alter table T modify constraint C disable;


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.