materialized view takes hours
This materized view takes 17 hours. the two tables have 4 million rows approximately. POS_MODS has 4mil and item_master has 10 mil rows.
IS there anything I can do speed up building materialize views.
Code:
create materialized VIEW POSINT.POSITEMPROMOTIONS_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS
SELECT
pm.pricing_method,
pm.prom_type,
pm.create_date,
pm.approval_date,
pm.start_date,
pm.start_time,
pm.item,
pm.discount_type,
pm.discount_amt,
pm.threshold_num,
pm.prom_id,
pm.seq_num,
pm.store,
pm.end_date,
pm.end_time,
pm.ends_in,
pm.immediate_dl,
getnetcost(storeitems.item_parent, pm.store, pm.prom_id) netcost,
getmfgroupid(pm.store, pm.pricing_method, pm.prom_id, pm.seq_num) mf_group_id
FROM
pricemethod pm,
(select /*+ INDEX(pm XXX_POS_MODS_REF_ITEM)*/
pm.store, pm.ref_item, im.item_parent, im.item_grandparent
from
pos_mods pm, item_master im
where
pm.ref_item = im.item) storeitems
WHERE
pm.store = storeitems.store
and (pm.item = storeitems.ref_item OR pm.item = storeitems.item_parent OR pm.item = storeitems.item_grandparent)
