Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: INSERT...SELECT... behavior different than SELECT behavior
Thank you for your response. We are running with OPTIMIZER_GOAL = CHOOSE,
which should default to ALL_ROWS, but your response got me thinking. I ran a
whole series of tests and found some very strange behavior:
These are the results:
Query SELECTINSERT...SELECT...
RULE 210 seconds 210 seconds CHOOSE 50 seconds 210 seconds ALL_ROWS 50 seconds 50 seconds FIRST_ROWS 50 seconds50 seconds
It seems that the SELECT statement is acting like I would expect it to. The cost-based optimizer is doing a better job, and unless you explicity tell it to use RULE, you get the "correct" behavior.
The INSERT...SELECT... seems to act like expected with the ALL_ROWS and FIRST_ROWS hints, but is acting strange if you specify no hint (it seems to be using the rule-based optimizer), or even if you use the hint CHOOSE. As mentioned above, all tables are ANALYZEd and OPTIMIZER_GOAL is set to CHOOSE, so we would expect the cost-based optimizer to be chosen in both of these cases!
I have opened a TAR on this issue with Oracle. Any ideas?
Thanks,
Gary
Jonathan Lewis wrote in message
<930863307.4309.1.nnrp-07.9e984b29_at_news.demon.co.uk>...
>
>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 Wed Jul 14 1999 - 16:39:50 CDT