Home » Server Options » Replication » Regarding materialized view
Regarding materialized view [message #3558] Thu, 03 October 2002 13:09 Go to next message
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 Go to previous message
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.
Previous Topic: White Paper on Resolving Network Problems in Oracle Snapshot Replication
Next Topic: ORA-00232 snapshot controlfile is nonexistent, corrupt, or unreadable
Goto Forum:
  


Current Time: Thu Mar 28 14:43:38 CDT 2024