Solved

How can I update or insert  a table  without using rollback?

Posted on 2002-05-09
20
2,692 Views
Last Modified: 2008-01-16
Hello Friends,

Which commands do I have to use to update or insert without using rollback segment temprorily?

Best regards,

Hüseyin

0
Comment
Question by:hbiyik
  • 6
  • 6
  • 4
  • +3
20 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
There is no way that I know of to bypass creation of rollback information for updates.  Some types of inserts can bypass creation of redo-log information, but whether they bypass creation of rollback information or not, I am not sure.  These inserts are limited to tables that you have altered to "nologging" and they only include "insert into...select... from" statements if you include the hint /*APPEND */ after "insert", or to SQL*Loader in direct path mode, if you specify "unrecoverable" in the control file.  Remember, if you use "nologging" temporarily, you usually will want to take a backup very soon and alter the table back to "logging" for normal use.
0
 
LVL 5

Expert Comment

by:Bajwa
Comment Utility
You cannot bypass rollback segments.  
0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
What's the reason you want to bypass rollback segments? Is it the insert size or the performance? Like Bajwa said, you can't bypass rollback segments, but you probably don't need to.

SQL*Loader in direct path mode is the fastest way to insert data into Oracle. If it's already in the database, the insert /* APPEND */ trick is your best bet.

If you need to insert a whole bunch of data and your rollback segment isn't large enough, you can break up your insert by creating a PL/SQL block that does several inserts instead of one big one.
0
 
LVL 4

Expert Comment

by:MFK
Comment Utility
You cannot bypasss....but can use the COPY Command to speed up and control the operation.
0
 

Author Comment

by:hbiyik
Comment Utility
how can I use it?
0
 

Author Comment

by:hbiyik
Comment Utility

how can I use the command below?
Could you please give me one example?
insert /* APPEND */

How can I do it for update statements?
I'm already database side.



Hüseyin.
0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
First you have to set your tables to nologging:

alter table mytable nologging;

Here's an example of the insert /* APPEND */. You'd use it to move data from one table to another:

insert /* APPEND */
into mytable
(col1, col2, col3)
select col1,col2,col3 from othertable;

To my knowledge you can't do anything like that for update statements. However, here's how you can do large updates without running out of rollback space, by committing every 1000 records:

declare
  commit_counter number := 0;
  cursor c is
    select col1,col2,col3,rowid from mytable;
begin
  for rec in c loop
    update mytable
    set col3 = col3 + 1
    where rowid = rec.rowid;
 
    if (commit_counter >= 1000) then
      commit;
      commit_counter := 0;
    end if;
  end loop;
end;
/
0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
oops! after the update statement, add:

commit_counter := commit_counter + 1;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
No, you cannot turn off logging for updates.  And there is no way to turn off rollback generation for updates.

Do you have a large update process that you need to do, and you are having a problem with rollback segments?  If you give us some more details about the specific problem you have, we may be able to suggest other ways to get the job done.
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility

 During DML operations major expense is logging, not use of rollbacks. Rollbacks are mandatory to provide read consistency.

 You can disable logging and updates/inserts will be flashing fast:
 
  alter table <name> nologging;

Thanks,

 rwarsh
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
I'm not sure where rwarsh is getting his information.  Here is an excerpt from the Oracle8.1.7 documentation:

SQL Statements That Can Use No-Logging Mode

Although you can set the NOLOGGING attribute for a table, partition, index, or tablespace, no-logging mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute. Only the following operations can make use of no-logging mode:

direct load (SQL*Loader)

direct-load INSERT

CREATE TABLE ... AS SELECT

CREATE INDEX

ALTER TABLE ... MOVE PARTITION

ALTER TABLE ... SPLIT PARTITION

ALTER INDEX ... SPLIT PARTITION

ALTER INDEX ... REBUILD

ALTER INDEX ... REBUILD PARTITION

INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

All of these SQL statements can be parallelized. They can execute in logging or no-logging mode for both serial and parallel execution.

Other SQL statements are unaffected by the NOLOGGING attribute of the schema object. For example, the following SQL statements are unaffected by NOLOGGING mode: UPDATE and DELETE (except on some LOBs, as noted above), conventional path INSERT, and various DDL statements not listed above.

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
If you really need to do a major update without generating rollback activity, the only way I know of is a three step process:

1. write an SQL script that will gather the data you need and write the updated data to a spool file (or use a PL\SQL procedure to gather the data, modify it and write it to an ASCII file with utl_file)

2. truncate the table (this generates no rollback and little or no redo)

3. use SQL*Loader in direct-path mode with "unrecoverable" specified to load the modified data back into your table (this generates very little or no rollback, and very little redo).
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility

Hello markqeer,

 Statement "insert /*+ append */ ... select ... " on nologging table will do work with the minimal (almost no) redo.

 However, You are correct about updates - they will generate redo.

Thanks,

 rwarsh
0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
rwarsh was correct about insert /*+ APPEND */ -- I accidentally left out the "+"
0
 

Author Comment

by:hbiyik
Comment Utility
Thank you rwarsh! I evaluated your answer but sorry yoren's comments was better than yours.

Best regards,

Hüseyin.
0
 
LVL 7

Accepted Solution

by:
yoren earned 100 total points
Comment Utility
Glad to be of help Huseyin. Here's the code I posted earlier with the corrections, for your convenience:

Inserting:

insert /*+ APPEND */
into mytable
(col1, col2, col3)
select col1,col2,col3 from othertable;

Updating with commits every 1000 records:

declare
 commit_counter number := 0;
 cursor c is
   select col1,col2,col3,rowid from mytable;
begin
 for rec in c loop
   update mytable
   set col3 = col3 + 1
   where rowid = rec.rowid;
 
   commit_counter := commit_counter + 1;
   if (commit_counter >= 1000) then
     commit;
     commit_counter := 0;
   end if;
 end loop;
end;
/
0
 

Author Comment

by:hbiyik
Comment Utility
Thank you rwarsh! I evaluated your answer but sorry yoren's comments was better than yours.

Best regards,

Hüseyin.
0
 

Author Comment

by:hbiyik
Comment Utility
Thank you yoren for your help. It was good.

I'm giving my last points.

Yoren I've just one question.
How can I see whether my table is working with nologging mode or not?

Best regards,
Hüseyin.

0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
Use the "logging" column of the user_tables view:

select table_name,logging
from user_tables;
0
 

Author Comment

by:hbiyik
Comment Utility

Sorry, I assume that I'll use APPEND trick at insert. IS it safe? When this process interrupted unexpectedly, may I face serious problem? Can I rollback it before commit it?    
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Delphi selector screen 2 57
Oracle Finace 3 43
sql for Oracle views 8 37
Oracle Syntax 8 39
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now