-
direct path insert
Hi Friends,
Oracle 9i on win2k
I have a transaction table with 4 million rows. I want to insert it
to our Master table of 50 million rows. Is there a fast insert in 9i (nologging, no temp) like the sqlloader direct path? I always experience that spooling this transaction table to text file and
sqlload it using direct path and rebuild indexes is still faster
than the normal insert command.
Thanks a lot
-
INSERT /*+ APPEND */ will do this. It has the NOLOGGING and PARALLEL options and generates no/minimum UNDO for the table - however the index updates will be logged and in UNDO.
-
Thanks a lot,
Anyway, I have to drop the index first like i used to do in sqlldr.
Do I need to alter the table to nologging?
Is this the right command?
sql> insert /*+ APPEND */ into MASTER select * from TRANS;
-
When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the blocks currently allocated to the table is not used.
If INSERT is parallelized using the PARALLEL hint or clause, then append mode is used by default. You can use NOAPPEND to override append mode. The APPEND hint applies to both serial and parallel insert.
The append operation is performed in LOGGING or NOLOGGING mode, depending on whether the [NO] option is set for the table in question. Use the ALTER TABLE... [NO]LOGGING statement to set the appropriate value.
-
Thanks dear,
So, these are the commands right...
sql> drop index MASTER_NDX;
sql> alter table MASTER nologging;
sql> insert /*+ APPEND */ into MASTER select * from TRANS;
If this will not gererates undo...I wont need to issue commit,
nor can i issue rollback. Am i right?
-
you still have to issue commit and you can still rollback.
Becuase you are inserting above the high water mark when you issue rollback it just forgets what it has done and commit just commits it
-
thanks dear,
So the difference from an ordinary insert is that the undo generated
for a 1 time commit 4 million records here using the /hint/ will not be as great i guess nor is the temp?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|