Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

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

Last updated: May 18, 2020 - 2:52 am UTC

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 Connor 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.

Rating

  (3 ratings)

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

Comments

Doubt

Su, May 12, 2020 - 4:51 pm UTC

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
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

A reader, May 13, 2020 - 6:39 am UTC

.. besides indexes and constraints.

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

Cheers!

A reader, May 15, 2020 - 12:26 pm UTC

How to do that disable both constraints and indes on target table ?
Connor McDonald
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.