Replies to This Discussion

Permalink Reply by Horace Simas on August 19, 2013 at 12:57pm

To some degree, materialized views are denormalized tables - the denormalization is whatever you can define in a SELECT statement, e.g. joins, aggregations, and analytic functions. After the original definition of the MV, you can add whatever indexes to the underlying MV table that are necessary to gain the performance you need.

Having said that, I think your options are:

1. Use denormalized tables in same database that are somehow maintained by code you write - This option will give you the greatest control over the loading process at the expense of having to write and maintain the code. You'll also eliminate the infrastructure overhead of a separate instance. The denormalization process and the reporting queries will add to the resource requirements of the active/transactional database and you must be sized to handle this. WIth this option you also have tied the availability of the reporting application to the availability of the transactional system.
2. Use MV's in the same database - The above comments about infrastructure and resource overhead apply, but you gain the leverage of using Oracle's MV functionality for scheduling (implemented via DBMS_JOB) and transactional read consistency (the "old" data is still visible until the new SELECT is resolved and committed).
3. Use MV's in another database/instance on the same host - You gain some marginal separation and potential availability with this option, but you are still affecting the overall resources of the database host. The later versions of Oracle let you control resource usage within the instance to a fine-grained level, so in my opinion there's no good reason to run a separate database on the same host.
4. Use MV's in another database on a different host - You can set up a db link to the transactional system and perform the MV refresh across the link. You'll still have the MV refresh/"load" process affecting the resources on the source system, but all query activity will be isolated and you'll have some degree of availability of reports during down time for the source system. You'll have to buy additional Oracle licenses with this option.
5. Use a Data Guard instance - Disadvantages: additional licenses, increased complexity to set up and administer. Advantages: lowest impact on the source system, true copy of system and if you use logical as opposed to physical replication you can create additional structures (views, indexes, etc.) in the Data Guard Database.

• ▶ Reply

Permalink Reply by Rey Gaskin on August 19, 2013 at 12:58pm

The best option is to use another Data Guard instance (not schema, it have no sense) on another machine. In this case you can have an up-to-date database an no disturb the production application.

About using materialized views is good if the refresh does not produce a lot of resources usage. And if the queries against materialized views also doesn't waste too many resources.

A third option you don't talk about is to use Oracle Resource Manager which allows you to control the resources usage with a lot of possibilities.

Anyway, I prefer the first one (Data Guard) because you have a "Report dabatase" a "live- backup" on the same time.

• ▶ Reply

▶ Reply to Discussion