We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Oracle 10g: use undo tablespace instead of rollback segment for a materialized view?

Medium Priority
1,840 Views
Last Modified: 2012-05-11
Hi Experts:

    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:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm
   
    I have the following materialized view creation sql statement from old database:
CREATE MATERIALIZED VIEW my_mv
(
-- fields
)
        PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
        STORAGE(
                INITIAL 1024 K
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0
                FREELISTS 1
                FREELIST GROUPS 1
                BUFFER_POOL DEFAULT
                )
TABLESPACE calgis_T1
LOGGING
NOCACHE
NOPARALLEL
REFRESH COMPLETE
        ON DEMAND
        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

Open in new window


    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?

    Thank you.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, remove the that clause to use undo

one reason you might want to use a dedicated rollback segment is if your MV refresh is a particularly large transaction and you'd like to isolate it from everything else
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
as noted in previous post, it's usually not necessary to do the isolation of transactions.  You would simply add more space to your undo and not bother trying to use rollback

Author

Commented:
sdstuber:

    My materialized view can go up to about 30 million records with the following five fields
VARCHAR2 (10 Byte)
NUMBER
VARCHAR2 (25 Byte)
VARCHAR2 (250 Byte)
DATE
Do I need to do isolation of transaction?

Thank you.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks slightwv and sdstuber.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.