Home » SQL & PL/SQL » SQL & PL/SQL » SQL with and without "Parallel" option
SQL with and without "Parallel" option [message #227109] Tue, 27 March 2007 06:16 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hello,


I have got a very simple sql to insert record into the table


insert into tab1 ( columns......)
select /* + parallel(tab) */ * from 
tab where tab.indicator = 'P'



Above query was taking min 4/5 hours to insert 1.4 millions record, i dont understand if it a single table , running on unix platform, Oracle 9i

SQL was tested with and without Parallel option but there was no change in query behavior

Any suggestions to make this query faster,

why query does behave with or without "Parallel"

Thanks
Re: SQL with and without "Parallel" option [message #227112 is a reply to message #227109] Tue, 27 March 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What you wrote is the same as:

insert into tab1 ( columns......)
select /* + blah blah blah */ * from
tab where tab.indicator = 'P'

A hint is introduced by /*+ with any space between * and +

Regards
Michel

Re: SQL with and without "Parallel" option [message #227122 is a reply to message #227112] Tue, 27 March 2007 06:33 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Michel,

Please give me valuable , meaningful suggestions if you have any...

appreciated..

[Updated on: Tue, 27 March 2007 06:33]

Report message to a moderator

Re: SQL with and without "Parallel" option [message #227123 is a reply to message #227122] Tue, 27 March 2007 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove the space between * and +

Regards
Michel
Re: SQL with and without "Parallel" option [message #227132 is a reply to message #227123] Tue, 27 March 2007 07:18 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have you tried this option

disable the index.
insert /*+ append */ from <table_a> select ... from table_b;
commit;
rebuild the index.

I am not able to find any way of improvement in the performance by having a parallel hint in your select.
Previous Topic: Finding how much time a procedure takes...without having access to the code of the procedure!
Next Topic: DBMS_SQL: Bind list of values with select statement?
Goto Forum:
  


Current Time: Sat Dec 03 20:15:00 CST 2016

Total time taken to generate the page: 0.05855 seconds