Re: INSERT...SELECT... behavior different than SELECT behavior

Try providing the INDEX Hint on the Select Clause to force the Optimiser to Use the Best Index..

-Thiru

gary&lilia wrote:

> Hi,
>
> We have a relatively complex SELECT statement (it is joining multiple Views,
> each of which joins other Views and have GROUP BY statements) that we are
> using to populate a table by using the INSERT...SELECT... statement. When we
> run the SELECT statement by itself, everything runs as expected, and the
> EXPLAIN PLAN and trace show that the best access paths are being chosen.
> When we add the INSERT clause, however, the query runs about five times
> slower. When I examine the trace output, I find that Oracle is choosing a
> much worse index, thus resulting in about ten times more physical and
> logical I/O.
>
> Does anyone know why an identical SELECT statement would act differently
> than an INSERT...SELECT... statement? My guess is that the optimizer is
> getting confused because of the complexity of joining the Views together,
> because I can get the statements to run with similar performance by
> replacing some of the Views with Tables.
>
> By the way, although we are reading hundreds of thousands of rows, we are
> only returning and inserting 29 rows (because of the GROUP BYs), so there
> should be very minor overhead associated with the INSERT portion.
>
> Thanks for any help,
> Gary
Received on Thu Jul 01 1999 - 12:57:59 CDT