If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Insert using /*+ append */ hint not reduced my time

Hi,
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.

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.

Hi,
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
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 )

@Hrishy,

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?

Rgds
Abhay.

funky...

"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"