Home » SQL & PL/SQL » SQL & PL/SQL » PARALLEL query with ROWNUM
PARALLEL query with ROWNUM [message #403275] Thu, 14 May 2009 10:14 Go to next message
jaytav001
Messages: 4
Registered: May 2009
Junior Member

hi friends,

i am using following parallel query to insert 100 million data from sTable to dTable

insert /*+ PARALLEL(dTable,24) */ into dTable
(col6,col1,col2,col3,col4,col5)
select /*+ PARALLEL(sTable,24) FULL(sTable) */
ROWNUM,
col1,
col2,
col3,
col4,
col5
from sTable;

Here, sTable and dTable both are partition (24) table.
Normally this query takes 40 mins.

Now if i remove ROWNUM from above query such as

insert /*+ PARALLEL(dTable,24) */ into dTable
(col1,col2,col3,col4,col5)
select /*+ PARALLEL(sTable,24) FULL(sTable) */
col1,
col2,
col3,
col4,
col5
from sTable s;

Now it takes 15 mins to insert data, which means insert query with ROWNUM is very slow.

I want to use ROWNUM in my query. Is there any other alternative to do it ???

Re: PARALLEL query with ROWNUM [message #403281 is a reply to message #403275] Thu, 14 May 2009 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you post what and how it has been requested to you in your previous post:
http://www.orafaq.com/forum/m/401592/102589/#msg_401592
that will never receive any answer nor feedback from you, by the way. Is it worth to answer you?

Regards
Michel

[Updated on: Thu, 14 May 2009 10:23]

Report message to a moderator

Re: PARALLEL query with ROWNUM [message #403282 is a reply to message #403275] Thu, 14 May 2009 10:24 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If this helps the understanding, ROWNUM is assigned to a row, after if is fetched from the database and before any order by is executed.

Being manufactured by the query are runtime, ROWNUM is not part of a row's data. Thus it is possible and indeed common and expected that ROWNUM will change at any time in response to things even as simple as time of day which might determine load on a database which might affect load balancing done by oracle which might change a query plan... I made some of this up but you get the idea.

Is your use of ROWNUM consistent with what it is?

Maybe someone knows if use of ROWNUM negates parallel operations?

Kevin
Previous Topic: select statement in collections
Next Topic: loading with external table rejected records not written to bad or discard file
Goto Forum:
  


Current Time: Mon Dec 05 11:08:14 CST 2016

Total time taken to generate the page: 0.17939 seconds