Roll back Segments?

[badrinathn]

For a typical transaction like inserting data from one table to another table, which need not be rolled back at all, Is there any way we can by-pass the entries made by oracle into rollback segments.

There by the bulk inserts/updates and deletes can be made much faster?

Badrianth

[gpsingh]

What is the reqt, do u want to create a temp table , work on the data and then copy to real table.If yes, try looking into global temporary tables of 8.1.6.

Take Care
GP

[badrinathn]

That's exactly my requirement, but how do I use the "Global Temporary Tables"

[gpsingh]

Be sure to create a temporary tablespace with tempfile(not datafile).
sql>
create global temporary table b on commit preserve rows as select * from a;
sql >(same session) work on b, commit;
sql>(same session) ,
create table d nologging as select * from b;
sql> alter table d logging;

Take Care
GP

[badrinathn]

In my requirement, I have a text file containing all the account numbers to be deleted. There are about 6 lac accounts which needs to be deleted.
I load the text file into a temp table (using sqlldr) and then
delete from maintable
where account in (select acc_no from temp);

This takes a long time. I only want to delete from main for records in temp. Can I use this concept , If so How?

Hope I am clear
Badrianth

[Halo]

Use a loop or a procedure to delete x thousand rows at a time and then commit.
-
If the # rows to be deleted>half the # rows in the table, consider creating a temp table and loading it with the data that you want to keep in the db.

[rkulkarni]

Don't use temp table. This requirement looks like a typical PL/SQL task. Why don't you use io utilities and read a number of records at once and then use a delete?

HTH,

Ravi