slow insertion

[salmanucit]

i have a complex select statement which takes less then 1 second to execute and it returns almost 28000 rows, i have created a temporary table in which i need to insert the result returned by this select statement and now its taked 8 seconds to insert into temporary table.
I tried to use APPEND hint but insertion time is same.

Now what i did, i inserted the values in another table "TABLE_ONE" and it took same 8 seconds and then i wrote a select statemtn as follows

insert into my_temp_table select * from table_one

and it takes 1.5 seconds to insert.

folllowing are the findings

1) SELECET statement takes 1 second (returns 28000 records)

2) insertion made using this select, takes 8 seconds

3) once table is populated with these 28000 records and then insertio of these records into another table using SELECT takes 1.5 sesonds.

Why insertion using my select statement takes much time and if same records are in a table then its takes only 1.5 seconds ?

How to solve this issue?

The execution plan of select statement is same and perfect if i see it individually or with insert statement.
and can we use NOLOGGING clause in INSERT statement?

Regards

[Thomasps]

How many indexes you got in that table?

[salmanucit]

All things remaining the same

See the following tests

problem i am facing is actually difference in table-to-table insert and select-to-table insert. As you said that table to table insert is fast and same case is here.
All other things remaining the same for both tests

Test 1:

INSERT INTO my_table SELECT ....... /* a complex select statement involving some joins and some functions in it and returning 28000 rows /* .........

This takes 8 seconds


Test 2

I created a table temp_table(regular table) using the select statement i used in Test 1 (and it dafinately took about 8 seconds as much it took in insertion in test 1)

Now i used

INSERT INTO my_table SELECT * FROM temp_table.
It takes 1.5 second


Here is the question that If data is same which is to be inserted into the my_table in both Tests, Why first test is slow and how could i make this fast if possible ?
Remember that the SELECT statement i am using executes in only less then a second if executed individually.


I have test APPEND hint saveral times and it wors fine but its not working here if my INSERT is based on SELECT in my case here

[Thomasps]

Ok..

Select count(*) from complex query;
select count(*) from temp_table;

And see the difference.. I think your complex query is taking time not the insert.... Also, trace the session and see the differences.

[salmanucit]

I mentioned that my SELECT statement takes less than a second to execute and return 28000 rows which i need to insert into my_table.
execution plan is OK and execution plan is same for select statement if select statament is used with insert statement.

Once these 28000 rows inserted into my_table in 8 seconds and then if i insert these 28000 rows in another empty table with same structure while querying these 28000 rows from my_table, it takes 1.5 sesonds.
data is same
Select statement executed individually takes less then a second and if used with insert statement takes 8 seconds. Execution plan is ame in both cases

[Thomasps]

Set the events 10046 at level 8 and see what is happening.