• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1691
  • Last Modified:

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

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
huangs3
Asked:
huangs3
  • 3
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
huangs3Author 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.
0
 
sdstuberCommented:
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
 
huangs3Author Commented:
Thanks slightwv and sdstuber.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now