OraFAQ Forum: SQL & PL/SQL » Insert as select from Table

Home » SQL & PL/SQL » SQL & PL/SQL » Insert as select from Table (11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insert as select from Table [message #621611]

Thu, 14 August 2014 18:17

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

Hello,

I'm trying to insert as select from table.

Insert into tableA as Select co1, count(col2) from TableB Group By Col1;

This operation is taking lot of time as TableB is a huge table of around 100GB.

TableB is an IOT table, I want all the records from this table. Is there a way to improve this performance?

Thanks.

Report message to a moderator

Re: Insert as select from Table [message #621613 is a reply to message #621611]

Thu, 14 August 2014 18:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Yuvraaj wrote on Thu, 14 August 2014 16:17

Hello,

I'm trying to insert as select from table.
Insert into tableA as Select co1, count(col2) from TableB Group By Col1;

I don't believe you!

SQL> connect user1/user1
Connected.
SQL> create table tablea (col1 number, col2 number);

Table created.

SQL> create table tableb  (col1 number, col2 number);

Table created.

SQL> Insert into tableA as Select co1, count(col2) from TableB Group By Col1;
Insert into tableA as Select co1, count(col2) from TableB Group By Col1
                   *
ERROR at line 1:
ORA-00926: missing VALUES keyword

Report message to a moderator

Re: Insert as select from Table [message #621618 is a reply to message #621613]

Thu, 14 August 2014 23:14

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

Believe me Smile

Its a typo. All i tried to explain the scenario.

SQL>  create table tablea (col1 number, col2 number);

Table created.

SQL> create table tableb  (col1 number, col2 number);

Table created.

SQL> insert into tableb values (101, 9999);

1 row created.

SQL> insert into tableb values (102, 8888);

1 row created.

SQL> 
SQL> 
SQL> commit;

SQL> insert into tablea select * from tableb; 

2 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select  * from tablea;

      COL1	 COL2
---------- ----------
       101	 9999
       102	 8888


SQL> select * from tableb;

      COL1	 COL2
---------- ----------
       101	 9999
       102	 8888

SQL>

Report message to a moderator

Re: Insert as select from Table [message #621620 is a reply to message #621611]

Fri, 15 August 2014 00:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Is there a way to improve this performance?

Many... which all depend on your environment, concurrent workload, money... as always.

Report message to a moderator

Re: Insert as select from Table [message #621648 is a reply to message #621611]

Fri, 15 August 2014 07:23

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

One possibility might be,

Insert /*+ append */ into tableA as Select co1, count(col2) from TableB Group By Col1 nologging;

Report message to a moderator

Re: Insert as select from Table [message #621668 is a reply to message #621648]

Fri, 15 August 2014 12:47

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Need to remove the typo "as" Wink

Report message to a moderator

Re: Insert as select from Table [message #621669 is a reply to message #621648]

Fri, 15 August 2014 12:53

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

sql>Insert /*+ append */ into tableA Select co1, count(col2) from TableB Group By Col1 nologging;
Insert /*+ append */ into tableA Select co1, count(col2) from TableB Group By Col1 nologging
                                                                                   *
ERROR at line 1:
ORA-00933: SQL command not properly ended


Elapsed: 00:00:00.16
sql>

Report message to a moderator

Re: Insert as select from Table [message #621670 is a reply to message #621669]

Fri, 15 August 2014 12:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe you could search for and find the error by yourself instead of relying on us to do all your work.

Report message to a moderator

Re: Insert as select from Table [message #621671 is a reply to message #621669]

Fri, 15 August 2014 13:03

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If the aim of using nologging here is to reduce the redo, then it depends on various factors and not necessarily going to be nologging. Few discussions are here :
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869
http://www.oracle-base.com/articles/misc/append-hint.php

Report message to a moderator

Re: Insert as select from Table [message #621675 is a reply to message #621671]

Fri, 15 August 2014 13:29

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

Quote:

Maybe you could search for and find the error by yourself instead of relying on us to do all your work.

I'm doing that in parallel.

Report message to a moderator

Re: Insert as select from Table [message #621677 is a reply to message #621675]

Fri, 15 August 2014 13:57

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No, do it first and ask when you can't.
You are not the only one to need help, so don't waste our and others time to something you can find by yourself. When we spend time for you we don't spend it for others that may need more help than you.

Report message to a moderator

Re: Insert as select from Table [message #621678 is a reply to message #621677]

Fri, 15 August 2014 14:24

Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA

Senior Member

SQL> Insert /*+ append */  into tableA nologging select col1, count(col2) from tableB group by col1;

2 rows created.

SQL> commit;

Commit complete.

SQL>

Thanks.

Report message to a moderator

Re: Insert as select from Table [message #621679 is a reply to message #621678]

Fri, 15 August 2014 14:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Re: Insert as select from Table [message #621680 is a reply to message #621679]

Fri, 15 August 2014 14:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Insert /*+ append */ into tableA nologging select col1, count(col2) from tableB group by col1;
quantify the change in performance by the use of NOLOGGING

Report message to a moderator