Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.