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

> I started gagging at MERGE JOIN CARTESIAN more than one dozen times
> though the number of rows seems to be small and the number of FULL
> TABLE ACCESSES but it seems your problem is the INSERT not the SELECT
> if I recall correctly. Is that the case?

I need to look at those full accesses alright (this is still work in progress)

>
> What is the timing with and without the insert?

Select on its own: parts of a second

Insert added to select: 50 seconds

>
> What indexes exist where you are doing the insert?

The indexes aren't the issue as I ruled them out by trying the insert into an identical table with no indexes - still 50 seconds.

> How many rows are in each of the tables involved?

A couple of tables (partmaster and partregionlistprice) are a few hundred thousand, the rest are thousands down to tens.

I know a couple of indexes can improve the full scans but it's the fact that Oracle will currently do the select in such an efficient manner but ask it to insert the records from the select and the performance takes a nose dive that is confusing me. Using the all_rows hint in both the select and the insert select I would have thought would mean oracle should be treating the queries very similarly but it doesn't seem to be the case.

I may have to go down the route of using a temp table to store the required records from a sub-view in the select rather than ask Oracle to do the full job in one go but it seems like a defeat.

Thanks,
Mark

> 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
Received on Fri Sep 01 2006 - 05:31:33 CDT