Regarding materialized view [message #3558] |
Thu, 03 October 2002 13:09 |
sholan
Messages: 1 Registered: October 2002
|
Junior Member |
|
|
Hi,
We had a ordinary view as like below and I changed to a materialized view as below.After that when I run a pl/sql block which queries from the matl view taking very long time. When it is an ordinary view the query returns within 10secs but after creating matl view the query taking some minute to return.Could anyone tell me why its happening and what i should do for it.
CREATE MATERIALIZED VIEW viw_topic_data_user_perm
PCTFREE 5 PCTUSED 60
TABLESPACE oeperm
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24
NEXT NEXT_DAY(SYSDATE, 'MONDAY') + 15/24
AS
SELECT A.topic_id, A.user_id, A.permission_type, A.group_id, A.permission_id, b.parent_topic_id, b.topic_name, b.level_id, b.sort_order, b.enabled, b.visibility, b.information, b.path, b.popup_window, b.load_popup
FROM TBL_TOPIC_DATA_USER_PERM A, TBL_TOPIC b
WHERE A.topic_id = b.topic_id
UNION ALL
SELECT x.topic_id, y.user_id, x.permission_type, x.group_id, x.permission_id, z.parent_topic_id, z.topic_name, z.level_id, z.sort_order, z.enabled, z.visibility, z.information, z.path, z.popup_window, z.load_popup
FROM TBL_TOPIC_DATA_GRP_PERM x, TBL_USER_GROUPS y, TBL_TOPIC z WHERE x.group_id = y.group_id
AND x.topic_id = z.topic_id
|
|
|
Re: Regarding materialized view [message #3568 is a reply to message #3558] |
Fri, 04 October 2002 11:33 |
Andrew
Messages: 144 Registered: March 1999
|
Senior Member |
|
|
The materialized view is a NEW table which is created - you are selecting from that. By default the MV does not have the indexes on your original tables. Create the indexes you want on the MV. There is no problem with doing that. You'll probably only want non-unique indexes - not unique ones, primary keys etc. The integrity of what's unique or not is determined by the source tables.
|
|
|