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

Thanks for the suggestion. Our problem with using a HINT is that the SQL is dynamically generated, and we have no idea what is the better index at the time -- it would depend on the distribution of the data. In other words, we would have to duplicate the work of the optimizer.

Even if we could use a HINT, I am disturbed that the SELECT clause is identical in the two statements, but different indexes seem to be chosen. If I could understand the behavior, I may be able to code around it more easily.

Gary

tmgn wrote in message <377BAC27.38F685E9_at_excite.com>...
>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 - 14:54:51 CDT