Home » SQL & PL/SQL » SQL & PL/SQL » Insert as select from Table (11G)
Insert as select from Table [message #621611] Thu, 14 August 2014 18:17 Go to next message
Yuvraaj
Messages: 118
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.
Re: Insert as select from Table [message #621613 is a reply to message #621611] Thu, 14 August 2014 18:56 Go to previous messageGo to next message
BlackSwan
Messages: 23067
Registered: January 2009
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



Re: Insert as select from Table [message #621618 is a reply to message #621613] Thu, 14 August 2014 23:14 Go to previous messageGo to next message
Yuvraaj
Messages: 118
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> 



Re: Insert as select from Table [message #621620 is a reply to message #621611] Fri, 15 August 2014 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
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.

Re: Insert as select from Table [message #621648 is a reply to message #621611] Fri, 15 August 2014 07:23 Go to previous messageGo to next message
John Watson
Messages: 4812
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;

Re: Insert as select from Table [message #621668 is a reply to message #621648] Fri, 15 August 2014 12:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2522
Registered: May 2013
Location: World Wide on the Web
Senior Member
Need to remove the typo "as" Wink
Re: Insert as select from Table [message #621669 is a reply to message #621648] Fri, 15 August 2014 12:53 Go to previous messageGo to next message
Yuvraaj
Messages: 118
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>


Re: Insert as select from Table [message #621670 is a reply to message #621669] Fri, 15 August 2014 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
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.

Re: Insert as select from Table [message #621671 is a reply to message #621669] Fri, 15 August 2014 13:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2522
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
Re: Insert as select from Table [message #621675 is a reply to message #621671] Fri, 15 August 2014 13:29 Go to previous messageGo to next message
Yuvraaj
Messages: 118
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.
Re: Insert as select from Table [message #621677 is a reply to message #621675] Fri, 15 August 2014 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
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.

Re: Insert as select from Table [message #621678 is a reply to message #621677] Fri, 15 August 2014 14:24 Go to previous messageGo to next message
Yuvraaj
Messages: 118
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.
Re: Insert as select from Table [message #621679 is a reply to message #621678] Fri, 15 August 2014 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Re: Insert as select from Table [message #621680 is a reply to message #621679] Fri, 15 August 2014 14:44 Go to previous messageGo to next message
BlackSwan
Messages: 23067
Registered: January 2009
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
Re: Insert as select from Table [message #621832 is a reply to message #621680] Mon, 18 August 2014 17:40 Go to previous messageGo to next message
Yuvraaj
Messages: 118
Registered: January 2011
Location: California, USA
Senior Member
This improved the performance by 60%.

Thanks.
Re: Insert as select from Table [message #621833 is a reply to message #621832] Mon, 18 August 2014 18:10 Go to previous message
BlackSwan
Messages: 23067
Registered: January 2009
Senior Member
Yuvraaj wrote on Mon, 18 August 2014 15:40
This improved the performance by 60%.


do not tell us.
SHOW US!
Previous Topic: ORA-04052 Error - URGENT
Next Topic: MATERIALIZED VIEW on commit
Goto Forum:
  


Current Time: Fri Nov 28 15:54:18 CST 2014

Total time taken to generate the page: 0.49296 seconds