Materialized view creation taking too long

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • rahulkumar
    New Member
    • Jul 2011
    • 6

    Materialized view creation taking too long

    CREATE MATERIALIZED VIEW cust_sales_mv
    tablespace claims_5m
    BUILD IMMEDIATE
    REFRESH COMPLETE on demand with rowid
    disable QUERY REWRITE
    AS

    SELECT c.claim_number,
    c.status,
    c.date_received ,
    aag.state,
    p.pay_amount
    FROM claims.ardis_ac tive_groups aag,
    claims.claim c,
    claims.payment p
    WHERE aag.GROUP_ID = c.GROUP_ID
    AND c.claim_seq = p.claim_seq
    AND c.status IN ('O', 'C')

    UNION


    SELECT c.claim_number,
    c.status,
    c.date_received ,
    sp.state,
    p.pay_amount
    FROM claims.claim c, claims.payment p, claims.service_ provider sp
    WHERE c.claim_seq = p.claim_seq
    AND c.service_provi der_seq = sp.service_prov ider_seq
    AND c.status IN ('O', 'C')

    UNION


    SELECT c.claim_number,
    c.status,
    c.date_received ,
    mea.state,
    p.pay_amount
    FROM claims.claim c,
    claims.payment p,
    claims.claim_pr ofile cp,
    gman.mem_evd_ad dress@ardis mea
    WHERE c.claim_seq = p.claim_seq
    AND c.member_claim_ profile_seq = cp.claim_profil e_seq
    AND cp.ardis_member _seq = mea.mem_evd_seq
    AND c.status IN ('O', 'C');


    Create table as select statement takes very less time. Please let me know how do i optimize materialized view to make sure it gets created in a lesser time.
Working...