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

You may find that your database is running with FIRST_ROW optimisation, but

create table as select
and
insert .. select

are clearly candidates for ALL_ROWS optimisation (since there is no end-user to trickle the results to), so Oracle switches optimiser mode on you silently.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

gary&lilia wrote in message ...
>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.
Received on Thu Jul 01 1999 - 15:40:50 CDT