| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: INSERT...SELECT... behavior different than SELECT behavior
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
> >
Just some conjecture here...
A select statement alone could be strongly affected by optimizer settings (like first_rows for example)...where oracle 'knows' that you want data back asap...so you may end with (say) nested loop style of access..
In the insert statement, oracle would 'know' that it wants insert all of the rows in the query, so it would lean toward more of a merge/sort style of operation...
Basically what I am trying to say is that every sql will be treated upon its "merits" - you sql may be affected in the same way that running a query used as subselect may be treated differently to it being run in isolation...
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Jul 01 1999 - 10:02:10 CDT
 |
 |