Hi ALL,
11gR2
I am running this sql and it hangs for long time as in 5 hrs so I do abort it
I run it as system being a dba so there should be no issue in permissions?
create materialized view sample_mvw as
SELECT sur.item_description ,
sur.inventory_id ,
sur.class_name ,
sur.perpost ,
sur.category ,
atg.ordinal_position ,
atg.trade_area ,
atg.trading_channel ,
atg.trade_territory ,
atg.trading_channel_manager ,
atg.trade_area_manager ,
atg.trade_manager ,
SUM(DECODE(sur.order_category_code, 'RETURN', sur.cost_of_sales, 0))
rma ,
SUM(DECODE(sur.order_category_code, 'MIXED', sur.cost_of_sales, 0))
shipped
FROM jscus.salesupdate_r12 sur ,
jscus.ora12_ar_trading_group atg
WHERE sur.trade_area = atg.trade_area
AND sur.solomon_territory = atg.trade_territory
AND sur.perpost = atg.period_post
GROUP BY sur.item_description ,
sur.inventory_id ,
sur.class_name ,
sur.perpost ,
sur.category ,
atg.ordinal_position ,
atg.trade_area ,
atg.trading_channel ,
atg.trade_territory ,
atg.trading_channel_manager ,
atg.trade_area_manager ,
atg.trade_manager
/
But If I run this query it just completed fast.
select count(*) from (
SELECT sur.item_description ,
sur.inventory_id ,
sur.class_name ,
sur.perpost ,
sur.category ,
atg.ordinal_position ,
atg.trade_area ,
atg.trading_channel ,
atg.trade_territory ,
atg.trading_channel_manager ,
atg.trade_area_manager ,
atg.trade_manager ,
SUM(DECODE(sur.order_category_code, 'RETURN', sur.cost_of_sales, 0))
rma ,
SUM(DECODE(sur.order_category_code, 'MIXED', sur.cost_of_sales, 0))
shipped
FROM jscus.salesupdate_r12 sur ,
jscus.ora12_ar_trading_group atg
WHERE sur.trade_area = atg.trade_area
AND sur.solomon_territory = atg.trade_territory
AND sur.perpost = atg.period_post
GROUP BY sur.item_description ,
sur.inventory_id ,
sur.class_name ,
sur.perpost ,
sur.category ,
atg.ordinal_position ,
atg.trade_area ,
atg.trading_channel ,
atg.trade_territory ,
atg.trading_channel_manager ,
atg.trade_area_manager ,
atg.trade_manager)
/
Please help why my materialized view hangs?
Kind regards,
jc