This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,805 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Append mode

user11153253
user11153253 Member Posts: 273
edited Mar 14, 2014 10:31AM in General Database Discussions

Hi folks,

i have 2 tables named F571226,  F751226_10march in two different schema testdta, backupdta respectively

Note: F571226 doesnt have any data in testdta schema, both have same columns names

Now my requirement is to  insert data into F571226 from F571226_10march

but when i try to insert by below command it taking too time and failed

insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14

shall i specify each field (columns name)  instead of specifying  source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows

please correct my syntax if it is wrong

insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.

thanks & regards.

Tagged:
Hoek

Best Answer

  • Hemant K Chitale
    Hemant K Chitale Oracle DBA OCP OCE SingaporeMember Posts: 15,759 Blue Diamond

    >shall i specify each field (columns name)  instead of specifying  source table

    if the column structure is the same you don't need to specify the column names.  Only SELECT *

    alter session enable parallel dml;

    alter table testdta.F571266 nologging;

    alter table testdta parallel (degree 4);

    alter table backupdta.F571266_10Mar14 parallel (degree 4);

    insert /*+ PARALLEL*/ into testdta.F571226 value select /*+ PARALLEL */ * from backupdta.F571226_10march14;

    commit;

    A PARALLEL Insert is a Direct Path INSERT, so the APPEND hint isn't necessary.


    Hemant K Chitale

«1

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    IT IS WRONG!

    "VALUES" & SELECT  are mutually exclusive; use one  or the other & never both

  • Hoek
    Hoek Senior Developer Den Haag, Nederland | The Hague, The NetherlandsMember Posts: 16,089 Gold Crown
    edited Mar 12, 2014 1:51PM

    If you want to use Parallel DML, then you need to enable it through:

    alter session enable parallel dml;

    and then run the insert statement.

    See the docs for more tips/explanations:

    Oracle Database Search Results: parallel DML

  • user11153253
    user11153253 Member Posts: 273

    Hi sb92075,

    thanks for your quick reply.

    so should it be like this below.

    insert /*+ APPEND PARALLEL */ nologging into testdta.F571226   select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.


    thanks.

  • user11153253
    user11153253 Member Posts: 273

    Hi Hoek,

    thanks for your reply.

    before i run my above query i run this

    >alter session enable parallel dml;

    so need to specify PARALLE again in my query?  like below

    insert /*+ APPEND  */ nologging into testdta.F571226   select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.

  • Hoek
    Hoek Senior Developer Den Haag, Nederland | The Hague, The NetherlandsMember Posts: 16,089 Gold Crown
    edited Mar 12, 2014 3:00PM

    Yes, you alter the session first AND you still need to add the PARALLEL hint as well.

    As already said, you can find many examples in the docs, explore the link I gave you.

    One more thing: putting nologging there makes no sense (hint: it could be a hint).

    Hoek
  • Hemant K Chitale
    Hemant K Chitale Oracle DBA OCP OCE SingaporeMember Posts: 15,759 Blue Diamond

    >putting nologging there makes no sense (hint: it could be a hint).

    NOLOGGING is not a hint.  It can only be specified as an attribute.

    I would ALTER the target table to NOLOGGING before the INSERT (APPEND or PARALLEL)

    Hemant K Chitae

    Hemant K Chitale
  • Hemant K Chitale
    Hemant K Chitale Oracle DBA OCP OCE SingaporeMember Posts: 15,759 Blue Diamond

    >shall i specify each field (columns name)  instead of specifying  source table

    if the column structure is the same you don't need to specify the column names.  Only SELECT *

    alter session enable parallel dml;

    alter table testdta.F571266 nologging;

    alter table testdta parallel (degree 4);

    alter table backupdta.F571266_10Mar14 parallel (degree 4);

    insert /*+ PARALLEL*/ into testdta.F571226 value select /*+ PARALLEL */ * from backupdta.F571226_10march14;

    commit;

    A PARALLEL Insert is a Direct Path INSERT, so the APPEND hint isn't necessary.


    Hemant K Chitale

  • user11153253
    user11153253 Member Posts: 273

    Hi Hemant,

    i have done this by using below command.

    insert /*+ APPEND */ into testdta.F571226 select  /*+ APPEND */ *  from backupdta.F571226_10march14

    but incase of testda.F571226 (target table)  has some fields othe than backupdta.F571226  (source table)

    for example

    testdta.F571226                   backupdta.f571226_10march

    TSDCT                                     TSDCT

    TSNUMB                                  TSNUMB

    TSMCU                                    TSMCU

    TSLOCATION                           TSLOCATION   

    TSCTRY                             

    TSYR

    TSGROUPID                           TSGROUPID   

    TSEMVNO                              TSEMVNO        

    TSDESC01                              TSDESC01

    TSDEPOWNER                       TSDEPOWNER

    so my statement should be like this

    insert /*+ APPEND */ into testdta.F571226 select  /*+ APPEND */    TSDCT,TSNUMB,TSMCU,TSLOCATION,'  ',' ',' ',TSEMVNO,TSDESC01,TSDEPOWNER  from

    backupdta.F571226_10march14;

    please assiste me.

    Thanks & regards.

  • Hemant K Chitale
    Hemant K Chitale Oracle DBA OCP OCE SingaporeMember Posts: 15,759 Blue Diamond

    Yes, you can specify the mapped columns in the SELECT list and include NULL or ' ' (space) or 0 or a default date  (depending on the datatype) f or the columns in the target table not present in the source table.

    Hemant K Chitale


    Hemant K Chitale
  • user11153253
    user11153253 Member Posts: 273

    Hi Hemant,

    Thanks for sharing

This discussion has been closed.