Skip to Main Content
  • Questions
  • Execute multiple queries concurrently

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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 1000+ times

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.



Is this answer out of date? If it is, please let us know via a Review

More to Explore

Performance

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