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
Nirmala
Messages: 43
Registered: October 2004
Member
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
BUILD DEFERRED
ENABLE QUERY REWRITE AS
 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
rleishman
Messages: 3724
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
BlackSwan
Messages: 25046
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
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@BlackSwan

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

@Nirmala
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: Thu Dec 08 20:05:42 CST 2016

Total time taken to generate the page: 0.10231 seconds