Re: Materialized View vs. Manual summary job

Comments interspersed.

Christian Svensson wrote:

> Greetings all,
>
> We are currently in our Datawarehouse project using manual jobs to
> create summay tables for our datamarts. I am about to investigate if
> we can gain some performance of using Materialized Views(MV) to do the
> summary tables so to say. The datamarts affected by this are always
> inital load and not delta loads. The jobs are run every weekend.
>
> - Are there any performance gains using MVs to handle the summary
> tables creation versus to manual in a Sql job do the summary ?

Yes. I'd suggest you investigate materialized views at http://asktom.oracle.com

>
> - When new daily data arrives in our fact table, does the MVs refresh
> engine only do a new summary of only the newly arrived data or does it
> do a complete refresh ? (The anwser to this is directly affected the
> above...)

Depends on how you define your materialized views: There are many different refresh options. Investigate this further at http://tahiti.oracle.com

>
> Our environment is Solaris & Oracle 8.1.7.3

Patch to 8.1.7.4.

>
> Thanks for any comments on this.
>
> Best regards
>
> Christian Svensson

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)