| Performance with materialized view [message #161953] |
Wed, 08 March 2006 00:36  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
