Skip to Main Content
  • Questions

  • performance impact of writing and reading the table simultaneously

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aditya.

Asked: March 09, 2016 - 6:15 am UTC

Last updated: March 10, 2016 - 3:09 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

We have an data-ware house application which has two schema. One is active schema and the other inactive.
The inactive schema is available to front end users and the active schema is used for updating the data. At a particular time in the day we switch the active and inactive schema so that the latest data is available to the front end users.

Now there is a discussion to retain only one schema instead of two. The problem is there are many tables which are truncated and populated and if we go with only one schema then the users will not be able access those tables while the load is in progress.

What is the best solution for such issues. Does creating materialized view on the tables(which get truncated and loaded) help? Also would be there a huge performance impact while the table is being updated and data selected simultaneously

and Connor said...

Truncate is ddl, so no matter what you do, querying across a truncate is going to run the risk of returning spurious results (aka, no rows or queries crashing).

Materialized views are really no different. You have the *option* during refresh of doing it atomically (ie, delete-insert) or not (truncate under the covers). If you choose atomically, you may as well keep your current scripts and just change the "truncate" to delete-insert. (Which of course will take a lot longer and consume more undo/redo to do).

There is a performance cost for long running queries when lots of updates are occurring, because we need to preserve the read consistency of the data, but you could easily benchmark this.

I suppose the question is - why is there the push to move to one schema ? Is there some fundamental issue with your current approach ?

If there is an issue, and *have* to change to one schema, then you might want to investigate whether some of the 'truncate-repopulate' functions can be replaced with "capture-delta-and-just-apply-those" to keep the amount of delete/inserts down to a minimum

Hope this helps.

Rating

  (4 ratings)

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

Comments

Aditya, March 09, 2016 - 8:02 am UTC

Hi Connor,

Thanks for your reply.

The problem with current setup is .. if the data load is delayed that data does not get replicated until the next switch is done which leaves the users with not the latest data.

So we are in discussions on how best to load the data and same time make it available to the users without much impact on the performance.

I will explain with an example of what we are planning. There is a table t which currently gets truncated/inserted. We are planning to change this to merge and then create a mview on table t . The front end would access the mview and once the base table is loaded we can refresh the mview.

But there are close to 80 tables which we may need to create mview's. Is this a optimal approach or can this be done in any other better way??
Connor McDonald
March 10, 2016 - 3:09 am UTC

"if the data load is delayed that data does not get replicated"

Surely if the data load fails, whether its truncate or mview or anything for that matter, the users dont have current data no matter what ? In your current scenario, they'd stay on the "old" populated schema and you would not switch over because the "new" schema is not correctly populated.

If this was a merge etc, the same would apply - you have old data.

Not sure I follow what you're say here.

For me the process seems relatively straight forward:

- if deltas are small, merge then straight into the target
- if deltas as large, you need a 'reload' concept (whether that is 1 or 2 schemas, or similar scenario, doesnt really matter - you do what best suits).




Is this Dw or lots

A reader, March 09, 2016 - 4:16 pm UTC

Sounds like more of oltp than dw

First are you looking for real time or near Realtime refresh ?

Or once daily ?

Define your frequency of refresh

Second why if two schema approach is working you want to change it... Nothing else to work on ??

You may look into mview logs for refresh on commits if Realtime is needed. Or create your own triggers painfully.

Why the truncate and replace ?

Why not just add new data if it's DW. Scd type 1,2

Need to define your requirements more clearly before looking into any solutions.


"switch the active and inactive schema"??

Duke Ganote, March 09, 2016 - 5:17 pm UTC

The classic approach for data warehousing is to simply swap tables/partitions between the 'work' schema and the 'user' schema. The 'work' schema can be truncated/loaded as needed, then swapped with the 'user' schema, which is "almost instantaneous".

I don't see any advantage to consolidating into one schema.

Tom and Alberto Dell'Era discuss this option about halfway down the thread here: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:752030266230
Chris Saxon
March 10, 2016 - 2:41 am UTC

Thanks for your input.

Aditya Manda, March 10, 2016 - 5:28 am UTC

Yes ,agree . We are also planning to use the partition exchange and eliminate the need for an active and inactive schema.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.