Re: Insert-Select much slower than same select on its own

zzzzzz45_at_hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

It would have been helpful if you had posted the two explain plans.

It would be helpful to know what indexes exist on the table into which the insert is taking place.

Potentially this could be a case where HINTS are required. But I wouldn't want to jump that far yet. As Jonathan Lewis has said, and I hope I am not misquoting, FULLY HINT if you want to force a particular execution path.

But first lets see those explain plans generated with DBMS_XPLAN.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org