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

[mallma]

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

[hrishy]

Hi

Drop all the indexes and then try the insert append.Otherwise i dont think so that direct path insert even works.

regards
Hrishy

[mallma]

Hi,
I tried removing the indexes, even its taking the same time.
any other go?

thanks.

[marist89]

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.

[dbtoo]

http://download-west.oracle.com/docs...s.htm#CNCPT128

[rpa]

Try following
(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.

[davey23uk]

insert /*+append*/ IS nologging effectively no redo is generated for the table

next extent advice is just stupid and wrong

[mallma]

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

[abhaysk]

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.

[mallma]

Hi Abhay,
I didn't get your question,
where can I get the arraysize?

thanks
mallma