Skip to Main Content
  • Questions
  • Execute multiple queries concurrently

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: January 10, 2020 - 4:37 pm UTC

Answered by: Connor McDonald - Last updated: January 13, 2020 - 4:14 am UTC

Category: SQL - Version: Oracle 12C

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Transaction subtleties

You Asked

Hi Tom,

I have a table having 2400 select queries. I am having one procedure which is calling and executing each queries one by one and updating the same table but different column. It is taking more than 1 hrs. How can we execute multiple queries at the same time to reduce the run time.
I have already added parallel hints in each queries in the table.

Thanks,
Ravi

and we said...

"updating the same table but different column


sounds to me like you do NOT want to run things concurrently, because if they are competing for the same *rows*, then you're going to have all sorts of concurrency/locking issues

As long as you can separate out the code into chunks that will NOT get you into a giant locking mess, you could use DBMS_JOB or DBMS_SCHEDULER to run them in parallel.

The other thing to look at is some refactoring of existing code if possible.

For example:

update MY_TABLE t
set COL1 = ( select abc from OTHER_TABLE where ... )

update MY_TABLE t
set COL2 = ( select def from OTHER_TABLE where ... )

update MY_TABLE t
set COL3 = ( select xyz from OTHER_TABLE where ... )


might be able to recast as

update MY_TABLE t
set (COL1,COL2,COL3) = ( select abc,def,xyz from OTHER_TABLE where ... )


and so forth.


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.