Thread: Materialized View question
08-15-03, 17:02 #1Registered User
- Join Date
- Jul 2003
Unanswered: Materialized View question
I have the following MV that I want to implement.
Since I have not created logs before I wanted some advice/help.
Basically, the 3 tables that are join RARELY get inserted into. IF a row gets inserted, I need the MV to auto-update. Would the REFRESH FAST ON COMMIT take care of this??
Otherwise, what else would I need to do?
CREATE MATERIALIZED VIEW ACTIVE_DEVICE_MV
refresh fast on commit
SELECT ad.org_id, ad.device_id, ad.cust_id, a.CREATED_DT
FROM ACCOUNT_DEVICE ad, ACCOUNT a, GATEWAY_PREMISE gp
a.org_id = ad.org_id AND
a.org_id = gp.org_id AND
a.svc_type_cd = 'E' AND
a.svc_type_cd = ad.svc_type_cd AND
a.cust_id = ad.cust_id AND
a.cust_id = gp.cust_id AND
a.prem_seq_nbr = ad.prem_seq_nbr AND
a.prem_seq_nbr = gp.prem_seq_nbr AND
a.active_ind = 'Y' AND
gp.active_ind = 'I'AND
gp.org_id = ad.org_id AND
gp.cust_id = ad.cust_id;
you can lead someone to something but they will never learn anything ...
08-15-03, 21:25 #2Drunkard
- Join Date
- Nov 2002
- Desk, slightly south of keyboard
Yes the main options are to refresh the MV on commit, or on demand. This is very much driven by whether the view is queried rarely and how quick you want that query to perform.
If you're getting 10,000 inserts daily on the tables but only one query on the view then probably on demand might be the better option.
Other more complex options include building two MVs on one table. One (the larger) which covers age old data and isn't refreshed and one which covers say the last month on a good selective index. A separate view unions the two. The larger, older MV is refreshed via an overnight job and the smaller 'current' view is refreshed on demand or on commit.
Separating old data which will never (unlikely) change from current data like this can offer huge performance advantages.