?
Solved

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

Posted on 2011-04-29
6
Medium Priority
?
1,613 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.
0
Comment
Question by:huangs3
  • 3
  • 2
6 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1800 total points
ID: 35493199
The docs seem to say to just remove that clause:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#SQLRF01302

USING ROLLBACK SEGMENT Clause
This clause is not valid if your database is in automatic undo mode, because in that mode Oracle Database uses undo tablespaces instead of rollback segments. Oracle strongly recommends that you use automatic undo mode. This clause is supported for backward compatibility with replication environments containing older versions of Oracle Database that still use rollback segments.

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35493208
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35493216
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:huangs3
ID: 35493346
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.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 35493455
it's not the output, it's the query to generate it.

and, as noted in the first post, it might not be possible to use rollback if your system is already using undo

and you probably don't need to anyway.


if you are managing all your transaction space in rollback segments then do a refresh, monitor your rollback usage.  If the refresh consumes so much that other transactions don't have room, then yes, you probably want to isolate it to it's own rollback segment.

If you are using undo, then simpy add more space to your undo
0
 

Author Closing Comment

by:huangs3
ID: 35513880
Thanks slightwv and sdstuber.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question