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.
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.