Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Refreshing Materialized View

    CREATE MATERIALIZED VIEW IND_UNMATCHED_MV
    PARALLEL
    BUILD DEFERRED
    REFRESH COMPLETE ON DEMAND
    AS
    SELECT
    aolindividualid
    ,truvuebestadressid
    ,infirstname
    ,inmiddlename
    ,inlastname
    ,rownum row_number
    FROM individual
    WHERE
    aolindividualid NOT EXISTS (
    SELECT /*+ HASH_AJ */
    aolindividualid
    FROM individual_tmp
    ) ;

    DBMS_MVIEW.Refresh('IND_UNMATCHED_MV', 'C');

    THIS REFRESH FUNCTION IS WORKING FINE WHEN BASE TABLE HAS 50 MILLION ROWS .

    BUT REFRESHING FOR 150 MILLION OF ROWS IT'S GIVING THIS ERROR :

    ORA-12008: error in materialized view refresh path
    ORA-12801: error signaled in parallel query server P014 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


    My questions are :
    1. Why it's indicating tablespace TEMP while I'm doing this materialized view in default tablespace ?

    2.Does "NOT EXISTS " degrade the functionality of materialized view?

    Any help will be appreciated ..
    himridul

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Refreshing Materialized View

    1) Presumably the TEMP tablespace is being used to build the hash table or something as an intermediate step.

    2) That syntax is wrong - it must be either:

    WHERE
    aolindividualid NOT IN (
    SELECT /*+ HASH_AJ */
    aolindividualid
    FROM individual_tmp
    ) ;

    or

    WHERE
    NOT EXISTS (
    SELECT /*+ HASH_AJ */
    aolindividualid
    FROM individual_tmp
    WHERE individual_tmp.aolindividualid = individual.aolindividualid
    ) ;

    You may find that the NOT EXISTS version is faster.

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    Sorry Andrewst , in the actual code it was NOT IN .
    I made the mistake.

    "Presumably the TEMP tablespace is being used to build the hash table or something as an intermediate step." -
    But I didn't mention USE_HASH hint in the query . And now I came to know that in SID , HASH_AJ has been also removed.Only PARALLEL hint is there.

    Other than this , how to resolve the errors ?
    What should I do to refresh the view properly for 150 millions of data?
    himridul

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I expect that if you rewrite using NOT EXISTS it may be quicker. The performance has little to do with materialized views per se, you can verify just by running the query. Make sure your statistics are up to date.

    If it still needs more TEMP space, can you not simply get your DBA to give you more?

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I would have to ask:
    1. Is the default next size for temporary tablespace set up properly
    (64) ... If that's the same value as sort_area_size, then I guess so...

    2. Does temporary tablespace need to extend and there is no room??

    3. The default max extents for the temporary segment must be set to
    a value, and you need more.... probably 128 is the current setting.

    either set the default next size to something that is reasonable for your environment or set the max # of extents higher.

    HTH
    Gregg

  6. #6
    Join Date
    Jan 2004
    Posts
    20

    Re: Refreshing Materialized View

    Originally posted by himridul
    CREATE MATERIALIZED VIEW IND_UNMATCHED_MV
    PARALLEL
    BUILD DEFERRED
    REFRESH COMPLETE ON DEMAND
    AS
    SELECT
    aolindividualid
    ,truvuebestadressid
    ,infirstname
    ,inmiddlename
    ,inlastname
    ,rownum row_number
    FROM individual
    WHERE
    aolindividualid NOT EXISTS (
    SELECT /*+ HASH_AJ */
    aolindividualid
    FROM individual_tmp
    ) ;

    DBMS_MVIEW.Refresh('IND_UNMATCHED_MV', 'C');

    THIS REFRESH FUNCTION IS WORKING FINE WHEN BASE TABLE HAS 50 MILLION ROWS .

    BUT REFRESHING FOR 150 MILLION OF ROWS IT'S GIVING THIS ERROR :

    ORA-12008: error in materialized view refresh path
    ORA-12801: error signaled in parallel query server P014 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


    My questions are :
    1. Why it's indicating tablespace TEMP while I'm doing this materialized view in default tablespace ?

    2.Does "NOT EXISTS " degrade the functionality of materialized view?

    Any help will be appreciated ..
    Hi,
    Concider the following quote from Advanced Replication guide
    "When a materialized view is created, several additional mechanisms are created at the materialized view site to support the materialized view. Specifically, a basetable, at least one index, and possibly a view are created "

    I had faced the same problem while creating a Snapshot on 120 Million rows but the sanpshot works fine on 10 M rows.In my case the refresh was failing because it was trying to create the defualt index .
    You can try including the using index tablespace clause

    CREATE MATERIALIZED VIEW IND_UNMATCHED_MV
    PARALLEL
    BUILD DEFERRED
    USING INDEX TABLESPACE <tablespace name>
    REFRESH COMPLETE ON DEMAND
    AS
    SELECT
    aolindividualid
    ,truvuebestadressid
    ,infirstname
    ,inmiddlename
    ,inlastname
    ,rownum row_number
    FROM individual
    WHERE
    aolindividualid NOT EXISTS (
    SELECT /*+ HASH_AJ */
    aolindividualid
    FROM individual_tmp
    ) ;
    Regards
    Satya

  7. #7
    Join Date
    Jan 2004
    Posts
    66
    Thanks Satya for your valuable information .
    I will try with this one .
    I will let u know the result .

    Thanks again ...
    himridul

  8. #8
    Join Date
    Mar 2011
    Posts
    2

    help

    Quote Originally Posted by himridul View Post
    CREATE MATERIALIZED VIEW IND_UNMATCHED_MV
    PARALLEL
    BUILD DEFERRED
    REFRESH COMPLETE ON DEMAND
    AS
    SELECT
    aolindividualid
    ,truvuebestadressid
    ,infirstname
    ,inmiddlename
    ,inlastname
    ,rownum row_number
    FROM individual
    WHERE
    aolindividualid NOT EXISTS (
    SELECT /*+ HASH_AJ */
    aolindividualid
    FROM individual_tmp
    ) ;

    DBMS_MVIEW.Refresh('IND_UNMATCHED_MV', 'C');

    THIS REFRESH FUNCTION IS WORKING FINE WHEN BASE TABLE HAS 50 MILLION ROWS .

    BUT REFRESHING FOR 150 MILLION OF ROWS IT'S GIVING THIS ERROR :

    ORA-12008: error in materialized view refresh path
    ORA-12801: error signaled in parallel query server P014 ORA-01652: unable to extend temp segment by 64 in tablespace TEMP


    My questions are :
    1. Why it's indicating tablespace TEMP while I'm doing this materialized view in default tablespace ?

    2.Does "NOT EXISTS " degrade the functionality of materialized view?

    Any help will be appreciated ..
    Based on my project, I need to update the materialized join view.
    I am using join queries.even if update query on join is executed, materialized join view is not updated. I want the update to be reflected on materialised join view, soon after the update query on any table constituting the join view is issued.

    Sir, Is there any option for this.I tried, but did not work like that.Is there any settings which we should modify in order to do so.

    First did like below
    create table T1(aa number,bb number)
    create table T2(bb number, name varchar2)
    insert into T2 values(1,a)
    insert into T2 values(2,b);
    insert into T2 values(3,c);
    insert into T1 values(1,100)
    insert into T1 values(2,200);
    insert into T1 values(3,300);

    then view is created.
    CREATE MATERIALIZED VIEW cust_sales_mvnew
    PARALLEL
    BUILD IMMEDIATE
    REFRESH COMPLETE
    AS
    Select T1.aa, T1.bb, T2.name from mm1 T1, mm2 T2 where T1.aa=T2.bb

    update T2 set name='hem' where bb=3
    then the table T2 is updated, not the view. when query rewrite is enabled true and the command is executed error is shown.

    ORA-00439: feature not enabled: Materialized view rewrite

    sir, even if it was set to true, the same error is showing..
    Sir, can you please help me.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Partitions, Views, and Other Schema Objects

    CREATE MATERIALIZED VIEW

    read the docs. you need to setup fast refresh on commit.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •