topic Re: Rollback disabling in Operating System - HP-UX

Rollback disabling

Manuel Gómez González — Fri, 31 Oct 2003 03:17:04 GMT

Hi all:

Does anyone knows how to prevent session or statement form rollback? Some way to disabling?

We are planning to delete a 25 million register table and donÂ´t want to have trouble with filled rollback segments.

I guess deleting speed would be improved too.

Thank

Re: Rollback disabling

Printaporn_1 — Fri, 31 Oct 2003 03:47:52 GMT

I guess that what best you can do in Oracle is assign your transaction to a specific RBS segment.
insert always use rollback segment.

Re: Rollback disabling

ALPER ONEY — Fri, 31 Oct 2003 04:02:28 GMT

Hi,
Regardless of which RDMS(sybase,oracle, ms sql server etc) you use, I guess that you can use minimally logged Transact Sql commands or some trace flags defined by your RDMS such as truncate table command, appropriate taceflags in sybase ASE to avoid filling up the logsegment.
HTH
ALPER Ã NEY
SYBASE DBA & REPLICATION ADMIN
I.S.E TAKASBANK I

Re: Rollback disabling

Manuel Gómez González — Fri, 31 Oct 2003 04:15:31 GMT

Database is ORACLE,

thanks

Re: Rollback disabling

Yogeeraj_1 — Fri, 31 Oct 2003 05:26:39 GMT

hi,
-
if you are deleting all the records, you would just do:
truncate table ;
-
If not and if hopefully you are using partitioning. You can do a mass delete in parallel using parallel DML (see the server concepts guide)
-
Otherwise, if 25 millions is a large percentage of the table, it is sometimes better to:
-
create table temp nologging as select * from t where id not in ( select id from a );
-
(keep the rows you want)
-
index temp (unrecoverable, in parrallel )
grant on temp (as you had for t)
drop table t;
rename temp to t;
-
If you need any further clarifications, please let us know.
-
regards
Yogeeraj

Re: Rollback disabling

Graham Cameron_1 — Fri, 31 Oct 2003 06:11:06 GMT

As per Yogeeraj, you can't disable rollback.

We have similar problems here with 600m row tables, and our approach is (similar as per Yogeeraj) to create a temporary table containing just the rows we want, then truncate the original, and copy back the rows, using either exp/imp or "select * from" - we're still testing.

Later we will be partitioning by date - these are date ordered records, 1 day = 1 partition, so in future we can just drop the partitions containing old data, and avoid rollback that way.

GOod luck

-- Graham

Re: Rollback disabling

Manuel Gómez González — Fri, 31 Oct 2003 06:18:30 GMT

Thanks guys:

Our target was disabling rollback because we have a great disk space lack, therefore we canÂ´t operate as you suggested (creating temporary tables, etc.).

WeÂ´ll create a PL/SQL for deleting small pieces of information each time.

Tha

Re: Rollback disabling

Graham Cameron_1 — Fri, 31 Oct 2003 06:29:21 GMT

Manuel

You can create a table with NOLOGGING, so it doesn't generate rollback. The NOLOGGING applies only to the table creation, but it will hold for create as select.

ie
CREATE TABLE mycopy
STORAGE (etc)
NOLOGGING
AS SELECT * FROM bigtable where ....

TRUNCATE bigtable ;

exp myycopy
imp into bigtable.

TRUNCATE doesn't create rollback, and insert generates little rollback (because there's no 'before' data to save), so you should get away with it.

-- Graham

Re: Rollback disabling

Claudio Cilloni — Fri, 31 Oct 2003 08:57:31 GMT

Do you have 1 or more columns that let you to subdivide you 25 million records into smaller subsets? So you could delete each subset separately and commit after each deletetion. This will reduce the need of rollback space,
but the deleting speed isn't good.

Keep an eye on the archive logs if you DB is in ARCHIVELOG mode.

hth,
Claudio

Re: Rollback disabling

Yogeeraj_1 — Fri, 31 Oct 2003 15:39:34 GMT

hi again,

of course you can do it using BULK COLLECT, however beware that if you have a table with more some number of indexes, deleting just does alot of work. Maintaining the index structures for xxx,xxx deletes
can be "cumbersome". Additionally there is lots of UNDO and REDO generated.

good luck
regards
Yogeeraj