Home » RDBMS Server » Performance Tuning » Performance with materialized view
Performance with materialized view [message #161953] Wed, 08 March 2006 00:36 Go to next message
reemagupta18
Messages: 3
Registered: February 2005
Location: Delhi
Junior Member
Hi All,
I am facing a performance hit with materialized view ( they r faster than the normal query but still it is slow)
The mat view is something like this

PROMPT Creating Materialized View 'ENT_PL_ACNT_CNT_MVW'
CREATE MATERIALIZED VIEW ent_pl_acnt_cnt_mvw
PARALLEL BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE(TRUNC(SYSDATE)+6/24, 'DD-MON-YYYY HH:MI')
WITH ROWID
AS
SELECT pltm.pool_id,
pltm.pool_trm_cmncmnt_date,
ent_pool_pkg.get_account_count_fn(pltm.pool_id, pltm.pool_trm_cmncmnt_date, 1, 'LVC_ACCOUNT_NAME') as CDA_LVC_ACNT_CNT,
ent_pool_pkg.get_account_count_fn(pltm.pool_id, pltm.pool_trm_cmncmnt_date, 2, 'LVC_ACCOUNT_NAME') as EDA_LVC_ACNT_CNT,
ent_pool_pkg.get_account_count_fn(pltm.pool_id, pltm.pool_trm_cmncmnt_date, 1, 'GSC_ACCOUNT_NAME') as CDA_GSC_ACNT_CNT,
ent_pool_pkg.get_account_count_fn(pltm.pool_id, pltm.pool_trm_cmncmnt_date, 2, 'GSC_ACCOUNT_NAME') as EDA_GSC_ACNT_CNT,
ent_pool_pkg.get_pool_new_acnt_count_fn (pltm.pool_id, pltm.pool_trm_cmncmnt_date) as TOTAL_NEW_ACNT_COUNT ,
ent_pool_pkg.get_pool_renew_acnt_count_fn (pltm.pool_id, pltm.pool_trm_cmncmnt_date) as TOTAL_RENEW_ACNT_COUNT
FROM ent_pool_term pltm
WHERE pltm.pool_term_status_id = 3
-- Excluding the SYSTEM POOL from pools selected in mat view
AND pltm.pool_id <> -1;

PROMPT Creating Index 'ENT_PL_ACNT_CNT_MVW_IDX1'
CREATE INDEX ent_pl_acnt_cnt_mvw_idx1
ON ent_pl_acnt_cnt_mvw (pool_id, pool_trm_cmncmnt_date);


These function calls are in turn calling some other tables on which count is performed and then stored as
MAT view.
Question : If I give the option fast refresh will it work, since the table in the mat view will not chnage but tables related to this on which I am performing the count will change.
Re: Performance with materialized view [message #221908 is a reply to message #161953] Thu, 01 March 2007 00:05 Go to previous message
vivekg19
Messages: 2
Registered: February 2007
Junior Member
i m also facing same problem i m creating mv based of india localizations table for balances
Previous Topic: Ref :"Understanding Indexes" from Home Page Link
Next Topic: Explain plan
Goto Forum:
  


Current Time: Sat Dec 10 14:58:14 CST 2016

Total time taken to generate the page: 0.04494 seconds