Rollback question

[Artur Undebarrena]

Hello.

I need to tell to Oracle database that do not use the rollback SQL
command to accelerate the delete command. I use databases in development
process and I don't need to rollback the inserts, updates and deletes. I
want to accelerate my sql queries and I think if I deactivate the
rollback command I'll win in speed.

Thank you very much.

[Jaap W. van Dijk]

You can't.

Rollback is not only used for rolling back but also for guaranteeing
that a query of another user sees the database as it is at the start
of the query: of all blocks that change during the runtime of the
query the original data is retrieved from the rollback the updating
processes have generated.

Jaap.

[Artur Undebarrena]

Then, Is it impossible to deactivate the rollback function?

Thanks.

En/Na "Jaap W. van Dijk" ha escrit:

[Thomas Kyte]

In article <3C5D43ED...@ictnet.es>, Artur says...

>
>Then, Is it impossible to deactivate the rollback function?
>
>Thanks.
>

with the exception of a very few commands -- yes. You might believe you don't
"need to rollback" but you do.

Suppose you were in the middle of a DELETE and the system failed. If we didn't
have rollback for it -- you would have a corrupt database, you lose

there are some commands that will bypass undo and redo generate, like a direct
path insert (insert /*+ append */ into t select ...), create table as select
nologging, create index nologging, but in general, all insert/update/delete
activity will generate undo.

--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp

[Jaap W. van Dijk]

My understanding is that, unlike the generation of redo information
which can often be switched off, undo information is always generated.
Or has this changed in Oracle 9i?

Jaap.

[Thomas Kyte]

In article <3c5d6a5e...@news.jaapwvandijk.myweb.nl>, j.w.v...@hetnet.nl
says...

>
>My understanding is that, unlike the generation of redo information
>which can often be switched off, undo information is always generated.
>Or has this changed in Oracle 9i?
>

no, non-logged operations do not need to generate undo either.

They typically work by performing their duties ABOVE the high water mark or in
temp segments that will be converted into real ones later. To "undo" them, we
just reset the high water mark.

Consider:

ops$tk...@ORA817DEV.US.ORACLE.COM> create rollback segment rbs_small tablespace
system
2 storage ( initial 32k next 32k minextents 2 maxextents 2 );
Rollback segment created.

ops$tk...@ORA817DEV.US.ORACLE.COM> alter rollback segment rbs_small online;
Rollback segment altered.

ops$tk...@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tk...@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects
where 1=0;
Table created.

ops$tk...@ORA817DEV.US.ORACLE.COM> set transaction use rollback segment
rbs_small;
Transaction set.

ops$tk...@ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects;
insert into t select * from all_objects
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 7
ORA-01628: max # extents (2) reached for rollback segment RBS_SMALL

ops$tk...@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tk...@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects
where 1=0;
Table created.

ops$tk...@ORA817DEV.US.ORACLE.COM> set transaction use rollback segment
rbs_small;
Transaction set.

ops$tk...@ORA817DEV.US.ORACLE.COM> insert /*+ APPEND */ into t select * from
all_objects;
17155 rows created.

ops$tk...@ORA817DEV.US.ORACLE.COM>

The first "logged" insert fails due to insufficient RBS space. The second one,
in the same RBS works....

[Yong Huang]

I almost want to say that it's an undocumented feature that nologging
also means no undo. I only know discrete transactions do not generate
undo although it generates redo. I finally find in Oracle8i Data
Warehousing Guide, Chapter 18
(http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76994/tuningpe.htm#53133)
that says:

When a table or index has NOLOGGING set, neither parallel nor serial
direct-load INSERT operations generate undo or redo logs.

In all other places of documentation that talks about nologging,
there's no mention of no undo.

Question for Tom: The create table statement does not have
"nologging". No "alter table t nologging" follows. How can t be
nologging, even though insert /*+ append */ is used?

Yong Huang
yon...@yahoo.com

Thomas Kyte <tk...@us.oracle.com> wrote in message news:<a3k2k...@drn.newsguy.com>...

[Mike Liu]

yon...@yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.02020...@posting.google.com>...

> I almost want to say that it's an undocumented feature that nologging
> also means no undo. I only know discrete transactions do not generate
> undo although it generates redo. I finally find in Oracle8i Data
> Warehousing Guide, Chapter 18
> (http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76994/tuningpe.htm#53133)
> that says:
>
> When a table or index has NOLOGGING set, neither parallel nor serial
> direct-load INSERT operations generate undo or redo logs.
>
> In all other places of documentation that talks about nologging,
> there's no mention of no undo.
>
> Question for Tom: The create table statement does not have
> "nologging". No "alter table t nologging" follows. How can t be
> nologging, even though insert /*+ append */ is used?
>

I guess the "logging/nologging" option will be ignored when database
in noarchivelog mode. But I can't find any documents about this.

Thanks,
Mike

[I hate Spam]

On Sun, 03 Feb 2002 13:16:05 +0100, Artur Undebarrena
<aundeb...@ictnet.es> wrote:

If you are deleting all rows in a table use trunc instead.

Yours Hans Erik Busk
t...@cn.stam.dk

[Jac]

You can bring the rollback segments offline, but like the expert said, u
can't tell Oracle not to use it. You can bring all the online segments
offline(except SYSTEM rollback segment - that's a no can do) but ur delete
statement will error out. Oracle searches for any available online rollback
segment in any tablespace for its use. If its doesn't find one - error (
this does not include the SYSTEM rollback seg).
Ur solution would be to create a pretty large rollback segment & set the
transaction(eg. delete) to use that particular rollback segment OR do the
delete using a procedure with a commit after every - say 1000 or more
deletes( depending on the size of ur rollback segment).
Hope this helps u.

"Artur Undebarrena" <aundeb...@ictnet.es> wrote in message
news:3C5D43ED...@ictnet.es...