This forum is currently locked. You can't register or post questions at this time. (read more)

INSERT IN APPEND MODE

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

younusdba
Member
Posts: 21
Joined: Mon Oct 28, 2013 5:24 pm

INSERT IN APPEND MODE

Postby younusdba » Wed Mar 12, 2014 6:00 pm

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: INSERT IN APPEND MODE

Postby Tim... » Wed Mar 12, 2014 7:01 pm

Hi.

If the structures are the same, you can just to the * (you don't need the word VALUE).

If the structures are not the same, it makes sense to list the columns.

Code: Select all

INSERT INTO t1 (col1, col2, col3)
SELECT cola, colb, colc FROM t2;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

younusdba
Member
Posts: 21
Joined: Mon Oct 28, 2013 5:24 pm

Re: INSERT IN APPEND MODE

Postby younusdba » Thu Mar 13, 2014 8:53 am

Hi tim,

thanks for your reply

i have done that by below statment

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: INSERT IN APPEND MODE

Postby Tim... » Thu Mar 13, 2014 9:05 am

OK. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ranitb
Member
Posts: 5
Joined: Tue Apr 08, 2014 11:04 am

Re: INSERT IN APPEND MODE

Postby ranitb » Tue Apr 08, 2014 1:58 pm

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


What is significance of using APPEND hint in the SELECT clause?

AFAIK - It is used for doing direct-path loads/inserts.

Please rectify me if getting wrong here.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: INSERT IN APPEND MODE

Postby Tim... » Tue Apr 08, 2014 3:36 pm

Hi.

The APPEND in the select is irrelevant. To be honest, I didn't eve see that. I was focussing on the APPEND in the insert. :)

If a hint is irrelevant, it is ignored. The APPEND hint in the query will be ignored. The one in the INSERT will not.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests