I have a table with 50 columns which is populated from 14 different tables.
slect part is taking 8 min. but when I insert into table explain plan is giving 9hrs.
I tried with both set and multiset tables and with and without group by. Nothing helped the load.
I tried changing PI also.
But intrestingly create table as (sel .........) with data completes in 8 min.
when I try to insert into new table ie empty table explain plan showing more time.
All the tables used in the query have collect stat on the require fields also.