Review
Geraldo Peralta, January 07, 2019 - 6:34 pm UTC
Thanks for the quick response.
Database A already has its own reports (customers selecting data the whole day). They (managers) just do not want to query the same database because they say Database A would be "slow".
Database B is like some business calculations based on data on database A. It is the book of purchases and sales.
January 08, 2019 - 1:31 am UTC
They (managers) just do not want to query the same database because they say Database A would be "slow".
"They" are probably wrong :-)
But anyway, materialized views are probably the easiest way forward here.
Review
A reader, January 08, 2019 - 1:56 am UTC
Yes, I think they are. But I'm just one dba against the world. Many think Oracle is like using MS Access. :D
Ok. Materialized views. So, it would use dblinks.
Questions?
1. Are MVs good for transporting lots of data?
2. Any advises to implement MVs for performance?
Thanks in advanced.
January 08, 2019 - 11:39 am UTC
1. You can fast refresh MVs across a db link (provided you stick to basic "select cols from tab"), so you only need to send the changes since the last refresh.
The initial setup will take longer. But this is a one-off hit. If this is an issue you could copy the table with data pump (or similar). Then build the MVs on the prebuilt tables.
2. Once you've built the MVs on the remote DB, you query them like regular tables. And you can whatever indexes you want - just like a normal table.
Review
Geraldo Peralta, January 08, 2019 - 11:54 am UTC
Thanks again for the quick response.
It has been very helpful!
January 08, 2019 - 12:27 pm UTC
Great to hear.
Review
Geraldo Peralta, January 10, 2019 - 11:48 am UTC
Hello, Team.
Just to know. Why MVs over any other option?
1. Why not Shareplex?
2. Why not SSIS?
...
Regards,
January 10, 2019 - 4:36 pm UTC
Straight back at ya: what's your reason for wanting to use these instead of MVs?
They both introduce another thing you need to setup, configure, and manage. Which means another thing which could go wrong.
If you're copying some tables as-is, MVs are about as simple as it gets.
Review
A reader, January 10, 2019 - 7:56 pm UTC
OK. I see.
When using MVs, how MV logs are maintained? Are they purged after some time?
January 11, 2019 - 6:07 am UTC
As you refresh a materialized view, entries in the materialized log are purged. They are self-managing in that regard - you don't need to do anything with them (besides refresh the materialized views)
Review
Geraldo Peralta, January 11, 2019 - 12:03 pm UTC
Great, Team!
Thanks for the quick and right response!
You're awesome!
Regards,
Review
Geraldo Peralta, January 14, 2019 - 4:37 pm UTC
I'm using MVs as you suggested.
Is there a way (keeping the MVs) to maintain the data in B database if it was deleted on A?
-- A business case
1. Customers sent 100 items in a XML invoice on January. (This data was replicated from A to B).
2. In February the customers said that he/she got wrong and actually it must be 70 items.
I will delete the data from A database but I need to keep it in B because of business tracking audit.
How can I do that if the MV refresh will delete data in B?
Regards,
January 14, 2019 - 5:45 pm UTC
Hmmm, it sounds like you're looking for a more comprehensive reporting solution here... More like a full-blown reporting data warehouse.
Likely the easiest is: DON'T DELETE!
Add a flag to the invoices table on the source DB to mark the invalid items as cancelled/rejected/whatever. When dealing with financial or other data you need audit reports on this is often the best way.
If you MUST delete from the primary, MVs probably aren't the way to go. You're looking at a more involved ETL process. Which can be a lot of work!
Review
Geraldo Peralta, January 15, 2019 - 12:54 pm UTC
Ok.
Can I insert directly in the MV and still have my automatic refreshes?
January 15, 2019 - 3:08 pm UTC
You can create updatable materialized views. But these lose your changes when you refresh. And they were deprecated in 12c.
It sounds like your requirement is a lot more complicated than just "we want to copy these tables to offload reporting". Helping you build a complete ETL process is beyond the scope of what we can do here. We can assist with specific Oracle Database/SQL questions you may have though.
Review
Geraldo Peralta, January 15, 2019 - 4:00 pm UTC
No problem.
You have helped a lot.
I will use SSIS for the ETL. I just wanted to have the best approach.
Thanks again.
January 15, 2019 - 5:14 pm UTC
No problem.
dream time
Racer I., January 17, 2019 - 8:09 am UTC
Hi,
Just some idle daydreaming :
If you use full refresh maybe you can drop the mview with PRESERVE TABLE instead. Then rename the table, recreate the mview and then full refresh.
Then maybe delete from the renamed copy all rows which exist in the refreshed mview.
If the deleted rows are few compared to the non-deleted ones copying them into a new table with a MINUS against the refreshed mview might be cheaper.
Then put a UNION ALL view over all the old copies and the mview itself and use it in your reports.
Or partition exchange them all into a partitioned table so the view can be static.
If the deleting is not randomly interwoven but by timestamp this becomes way more manageable. Especially if the table is partitioned in both dbs. Then you swap out the oldest partition into the other table in the reporting db before dropping it in the source db. Then the next refresh will not repopulate it in the standby db but its still there for reports via the UNION-view. I don't know if a mview table can be partitioned automatically but it should be possible to PRESERVE-drop the mview, partition the table and recreate the mview with USING.
Some planning ahead might be necessary. Like if the table has an ID using a cycling sequence.
Of course a dedicated warehouse/ITL application should do this automatically, more generically and robustly.
I know nothing about SSIS but the name suggestd an SQL Server is involved. This might mean that the data is copied twice and persisted in the intermediate SQL-server. This might be more expensive/slower than a single step approach.
regards
January 18, 2019 - 10:50 am UTC
Possible. But... sounds complicated! It sounds to me like they need a "proper" ETL/data warehousing solution.
Yes, SSIS is a Microsoft ETL product. I'll leave it up to readers to decide whether this is the "best" method for Oracle Database <> Oracle Database integration.
Review
A reader, January 18, 2019 - 11:09 am UTC
Thanks for the response, Racer I. Could be an option but it's a little complex.
@Chris, what other ETL solution you could recommend to work with Oracle database (source and target)?
Regards.
January 18, 2019 - 11:39 am UTC
I don't have enough experience with any ETL tools to recommend one to you.
In any case, which one is "best" for you depends on:
- Your current in-house skills
- What existing support and licensing agreements you have in place
- How much you're willing to spend (on hardware, support, licensing, consulting, extra staff, ...)
- Technical details of the implementation
- Non-functional requirements (performance, monitoring, etc.)
- ...
You also need to consider whether this a tactical solution to solve one specific requirement. Or if this will form the basis of a larger ETL/data warehouse/BI pipeline.
Ultimately you need to sit down with the people who want these reports to find out what information they need and why. This will help determine how much time you spending answering the above questions and choosing a technology.