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

Did U Analyze the Objects involved ?
In my experience I have seen Oracle 8' s optimiser behaving much better than that of Oracle 7.

-Thiru

gary&lilia wrote:

> 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 - 15:59:01 CDT