Re: Remote MVs.
- From: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>
- To: gurenich@xxxxxxxxx
- Date: Tue, 9 Jun 2009 08:16:23 +0200
Maria, I have put in solutions with remote MVs and taken out all depending on need and what solution fits the problem best. The time I put it into a solution as when we had 12 regional databases, but a lot of "reference" data needed to be maintained in one place. We ended up with over 100 tables we moved to the regional databases several times each day. This worked reasonably well due to the rate of change was fairly low and we didn't need the data to be current at all times. When I reworked a solution using MVs to not do it was when a solution had put in a solution where all reporting data (essentially all data in the database) was moved to a database on a different server using MVs. The load on I/O for writing to the logs, reading from the logs, network traffic and so on caused this to not be practical. It had a much bigger impact on the operational system than the actual execution of the reports. Part of the reason was that all data was replaced in this solution over 90 days. So we saw a rate of change on the largest tables of over 1% per day. With some volume that becomes a challenge to maintain and write over network. Especially when it has to be current within 15 minutes. The impact during the peak of the processing day was just not acceptable. What I would recommend is that you create a way for the reporting application to work such that you can change database later and also freedom to change the way your data is provided. I would do this by using an alias defined in TNS names as the "SID" used to connect to the database. Then you can switch it to a different database later. In the schema you put their objects in (preferably not the same as the schema the application uses), create views that performs the work you want the MVs to do. Now you can move the views, and even replace them with MVs in a remote DB if you find that as needed. You can even make the decision on view by view so you only accept the complexity for the views that has an unacceptable impact on your production system. That is, start with the simple solution of having the views defined in your current DB (different schema) and move gradually to MVs in remote schema if you need to. Another option in the middle is to create MVs in the local database if they do a lot of aggregations that taxes the current database too much and the data does not have to be current at all times. Mathias On Tue, Jun 9, 2009 at 6:18 AM, Maria Gurenich <gurenich@xxxxxxxxx> wrote: > Yo, ppl. > > I was asked if this is possible to have a database that would contain a > very specific views that are built from the source database objects so that > our security ppl could run their reports. IMHO, the ideal solution would be > building a replication site using MVs. > > My question to you is the following: I am not quite sure would there be any > impact on my source database as eventually the umber of MVs can grow if I > did everything correctly and everybody is satisfied with the result. > > The one who uses remote MVs - please, share your experience, pros and cons, > the speed of building replication environment, etc.. This is all is under > the investigation yet, so any suggestion would be greatly appreciated. > > Thanks a lot before hand, > M. >
- Remote MVs.
- From: Maria Gurenich
- Remote MVs.
Other related posts:
- » Remote MVs. - Maria Gurenich
- » Re: Remote MVs. - Mathias Magnusson