Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view creation. (Oracle 10g; Windows)
Materialized view creation. [message #402316] Fri, 08 May 2009 13:22 Go to next message
Messages: 43
Registered: October 2004
I created a script which creates a few Materialized views with the BUILD DEFERRED option. The script runs fine in my development environment but when i run the script in my test database which has much more data compared to the development environment my creation of the materialized view takes a very long time to complete in spite of creating the view with "BUILD DEFERRED" option. Did anyone come across this kind of problem? I am pasting below a sample create statement that i am using.

CREATE MATERIALIZED VIEW ms_rep_sales_sum_weekly_mv
 select /*+ full(a) parallel(a,4) */
                       replace(to_char(to_date(year_month_day,'yyyymmdd'),'IYYY-IW') ,'-','0') year_month_day,
                       nvl(a.rep_id ,0) rep_id,
                       nvl(a.off_id ,0) off_id,
                       nvl(a.firm_id ,0) firm_id,
                       nvl(a.ta_firm_id ,' ') ta_firm_id,
                       nvl(a.platform_cd ,' ') platform_cd,
                       nvl(a.prod_cd,' ') prod_cd,
                       nvl(a.soc_cd_grp,' ') soc_cd_grp,
                       nvl(a.sleeve_prod_cd,' ') sleeve_prod_cd,
                       nvl(a.primary_source,' ') primary_source,
                       sum(a.trade_purchases) trade_purchases,
                       sum(a.confirm_purchases) confirm_purchases,
                       sum(a.redemptions) redemptions,
                       sum(a.exchanges_in) exchanges_in,
                       sum(a.exchanges_out) exchanges_out,
                       sum(a.other) other,
                       sum(a.confirm_redemptions) confirm_redemptions
                       from test_table a
                 group by replace(to_char(to_date(year_month_day,'yyyymmdd'),'IYYY-IW') ,'-','0'),
                       nvl(a.rep_id ,0) ,
                       nvl(a.off_id ,0) ,
                       nvl(a.firm_id ,0),
                       nvl(a.ta_firm_id ,' ') ,
                       nvl(a.platform_cd ,' ') ,
                       nvl(a.prod_cd,' ') ,
                       nvl(a.soc_cd_grp,' ') ,
                       nvl(a.sleeve_prod_cd,' ') ,
                       nvl(a.primary_source,' ')

Re: Materialized view creation. [message #402439 is a reply to message #402316] Sun, 10 May 2009 08:13 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bit strange.

ALTER SESSION SET events '10046 trace name context forever, level 8';

Run this before you run it again, then exit your session. Use TKPROF to parse the trace file and post it here. It will show what was running and the wait events.

Ross Leishman
Re: Materialized view creation. [message #402448 is a reply to message #402316] Sun, 10 May 2009 10:53 Go to previous messageGo to next message
Messages: 25536
Registered: January 2009
Location: SoCal
Senior Member
Post EXPLAIN PLAN from the two different systems so we can compare them.
Re: Materialized view creation. [message #402538 is a reply to message #402448] Mon, 11 May 2009 07:26 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member

This isn't a post about Query performance, it's about the query being executed immediately, even though it's created as BUILD DEFERRED.

Could you show us a cut and paste of an SQL*Plus session demonstrating what's happening?
Previous Topic: DECODE and CASE Expression Limitation (merged 3)
Next Topic: SQL Query
Goto Forum:

Current Time: Tue Jul 25 12:16:56 CDT 2017

Total time taken to generate the page: 0.45101 seconds