0

I have 25 tables I refresh in one transaction. Few of them refresh much longer than others. I wonder would it possible to refresh "short-timers" in parallel of "long-timers" in same one transaction. One transaction is just for convenience to rollback changes on all tables in case of failure.

Not sure yet if I want to achieve something contradictory to "transaction" concept.

2 Answers 2

1

Not sure yet if I want to achieve something contradictory to "transaction" concept.

Yes, you do.

A Transaction is "atomic" - indivisible. You get all of it or none of it.
If you leak some "bits" of it ahead of other "bits", it's not atomic and it's not a Transaction.

Do all 25 have to delivered at the same time?
If not, then you could just lose the transaction altogether and let the tables become available as they are loaded (presumably, the refresh process locks the table, one way or another).

1

Oddly enough, I actually do this in SSIS.

Yes, it's a Microsoft Product, but by golly does it help me run ORACLE tasks in sequence and in parallel.

ETL tools (like SSIS, Datastage, Talend, etc.) will allow you to accommodate items that run in parallel, sequence, and sync/async. You can use the "workflow" layout to add sequencing, conditions, and alerts. Transaction control is also made to be much more manageable.

Definitely suggest you give an ETL tool a shot. Lots of good ones out there, I just use SSIS because it's free with Standard, and it's easy for me to use, enhance, and maintain.

2
  • 1
    Maybe putting each table refresh in a job, that puts its status into common table, and scan this table till the last refresh to see if any other job failed
    – Jakub P
    Jun 29, 2021 at 6:24
  • @JakubP Yeah for sure. Jobs in Oracle are a bit hairy but definitely get the job done. Definitely would need a control table regardless of the approach taken, IMO. That way it's easy to check up without having to look at ALL_SCHEDULER_JOBS or whatever that view is.
    – SQLDevDBA
    Jun 29, 2021 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.