Insert using /*+ append */ hint not reduced my time
I have given Append hint in the insert query which is loding data from remote DB,eventhough the time taken by the insert query is not reduced.
The table in which I am inserting is not having more than 2 indexes.
There select statment with union all is loading data from remote DB which is inserting 29000 records.Explain plan is using nested loops and index scan.
the select alone is taking seconds. but the insert is taking 2 mitues.
thanks in advance
Drop all the indexes and then try the insert append.Otherwise i dont think so that direct path insert even works.
I tried removing the indexes, even its taking the same time.
any other go?
You can try all sorts of different tweaks, but until you trace the process you don't have a clue as to what is taking the most time. With the +APPEND hint maybe you are taking a query that takes 3 minutes down to 2.5 minutes. Is it really worth it? Trace it.
Last edited by marist89; 07-01-2005 at 12:37 PM.
slow direct inserts
(1) Put table in nologging mode ( it will suppress redo generation for this table).
(2) make next extent size large if currently it is small.
insert /*+append*/ IS nologging effectively no redo is generated for the table
next extent advice is just stupid and wrong
Are you talking about the NOLOGGING in the create table command?.
If so then its is given in the Create script of the table itself.
if no please advice
Thanks in advance.
Originally Posted by mallma
I would guess that fetch of 30K records from remote DB.. is taking most of ur time.. you can do a simple test for urself and check it..
Wass ur Arraysize? ( it does matter for data over n/w )
Append indeed works if even indexes are there.. but only for table.. for indexes its a usual way to have redo recorded.. And again here your suggestion without even thinking?
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
I always drop or disable indexes on the table where i am doing a dataload and rebuild those indexes in parallel and nologging mode.Thats why i said i think so..wasnt really confident :-)
Originally Posted by abhaysk
Click Here to Expand Forum to Full Width