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_active_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_provider_seq = sp.service_provider_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_profile cp,
gman.mem_evd_address@ardis mea
WHERE c.claim_seq = p.claim_seq
AND c.member_claim_profile_seq = cp.claim_profile_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.