Home » SQL & PL/SQL » SQL & PL/SQL » Putting multiple processes on rownum selection
Putting multiple processes on rownum selection [message #316329] Thu, 24 April 2008 11:32 Go to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
All
I have a package in which the cursor does rows selection for processing on rownum basis. Now I want to spam couple of processes in parallel, I cannot do as the cursor does selection on rownum. They can select same rows in parallel process.

I used FOR UPDATE SKIP LOCKED in cursor, but it did not work. I am 10g R2, Please help how can I put multiple processes.

Thanks
Toshi
Re: Putting multiple processes on rownum selection [message #316332 is a reply to message #316329] Thu, 24 April 2008 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
FOR UPDATE SKIP LOCKED

This does not exist in 10g and below.
Or if you prefer it is a hidden featture that can only be used by Oracle.
You have problem with it, it does not surprise me. You don't know how to use it.

If you want to run 2 processes in parallel you have to know how to partition your data in 2 sets. If you don't how to do this, you can't parallel your process.
Use rowid instead of rownum if you don't have a primary key or other deterministic criteria.

Regards
Michel


Re: Putting multiple processes on rownum selection [message #316334 is a reply to message #316332] Thu, 24 April 2008 12:03 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
I clearly did not understand what you said. I have a primary key, but how it affects??

I do select * from table_a
where rownum<500

It processes this 500 rows. I want to make this process faster, but when I put another process. It does the same cursor??

select * from table_a
where rownum<500

Both might select the same set of row?? How do I use rowid. Please help...
Re: Putting multiple processes on rownum selection [message #316339 is a reply to message #316334] Thu, 24 April 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't use rownum which is a local number (local to the query).
Use a global criteria like primary key or something else in your table and if there is nothing in the data themselves to separate in partition, use rowid.

Regards
Michel
Re: Putting multiple processes on rownum selection [message #316375 is a reply to message #316339] Thu, 24 April 2008 18:11 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
Can you please help me on how to use rowid??
Re: Putting multiple processes on rownum selection [message #316393 is a reply to message #316375] Thu, 24 April 2008 23:22 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First give the description of the table, give the query you have to partition, as I said: use rowid ONLY if you can't partition by a logical way, can you?

You are an expert, explain why you can't.

Regards
Michel
Previous Topic: Materialized view does not refresh when remote connection is dropped temporarily
Next Topic: [ ORA-01031: insufficient privileges ] error while creating trigger
Goto Forum:
  


Current Time: Mon Feb 17 19:02:26 CST 2025