Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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).
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.
