From the Oracle 10g reference I know that for materialized view, the use of rollback segment is only for backward compatibality, and undo tablespace is a newer option:
I have the following materialized view creation sql statement from old database:
CREATE MATERIALIZED VIEW my_mv
PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
INITIAL 1024 K
FREELIST GROUPS 1
START WITH TRUNC(SYSDATE + 4/24) + 21.25/24
NEXT TRUNC(SYSDATE + 1) + 21.25/24
WITH PRIMARY KEY
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
-- sql query
Can you tell me:
1. how to change it to use undo tablespace? just remove the "USING DEFAULT LOCAL ROLLBACK SEGMENT" clause? I didn't see any description in the reference how to use undo tablespace in the SQL statement for materialized view.
2. is there any drawback on the refresh performance if I use undo tablespace instead of rollback segment? Is there any situation that I should not use it?