Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Don.

Asked: May 23, 2023 - 4:33 pm UTC

Last updated: June 07, 2023 - 7:24 am UTC

Version: 19c

Viewed 1000+ times

You Asked

We have a system in which reference data (about 100 tables) is created and maintained by a set SMEs in a specific database for this purpose. On frequent (by not regular) occasions, the schema is cloned to another schema which acts as a source for distribution to other databases. The reason for the second schema is so those SMEs are not interrupted for the duration of the distribution to 20ish other database/schemas.

We have significant problems with the hand-coded and maintained ETL process. For instance, when a new column is added to a reference table, someone must remember to add this to the ETL, and it is not infrequently forgotten. Dependency order of foreign keys can also bollix the process.

This distribution is on demand and not continuous, so it doesn't appear that regular database replication would work. Do you have any suggestions?

Second question, is it possible for delayed foreign keys and unique constraints to result in a non-acid compliant data condition.

Thanx in advance.


In response to your request for clarification, yes, the "ETL" is cloning of the base schema to the secondary schema. In this case, there is not transformation; it is a literal clone. There was a time when there was massive transformations, and the term just stuck.

and Connor said...

Can you clarify something: When you "ETL" are you referring to the cloning of the base schema to the secondary schema? Or something else?

=================

If it is just on demand, is there any reason a DataPump is not a potential option? It has plenty of options to crank up performance (parallelism etc).

If not, then CTAS or insert-select is also something is easily parallel-ised across tables (with the scheduler) or intra-table (with parallel dml)


re: question 2. I assumed you are referring to deferred not disabled constraints.

A deferred constraint will still check that the integrity rules still apply at commit time. For the session conducting the transaction, there could be a (temporary) moment where things are inconsistent, eg you've inserted an employee before the parent department, but you *chose* that option by allowing the constraint to be deferrable.


Rating

  (2 ratings)

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

Comments

A reader, June 02, 2023 - 2:28 pm UTC

Great, thanx. I'll look into DataPump and CTAS.

On the second question, Yes, I meant deferred. So a problem would occur if I did something stupid, in a stored procedure, like where I insert the child first, then try to do stuff, within the current transaction, before actually adding the parent, correct?
Connor McDonald
June 07, 2023 - 7:24 am UTC

Well., not really a "problem" because its all in the one transaction.

Of course, if you did something like:

- set deferred
- insert child
- now do a query which joins parent and child (assuming that if there's a child there MUST be a parent)

then the join would fail because you haven't inserted the parent yet. But that is still only within your transaction. No-one else can see either row.

A reader, June 07, 2023 - 2:44 pm UTC


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.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.