Printable View
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
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
That's exactly my requirement, but how do I use the "Global Temporary Tables"
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
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
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.
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