Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: INSERT...SELECT... behavior different than SELECT behavior

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

From: Gary Hirschhorn <ghirschh_at_hmsy.com>
Date: Wed, 14 Jul 1999 14:39:50 -0700
Message-ID: <uMHqh$jz#GA.470@cpmsnbbsa02>


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:

  1. We are already using the cost-based optimizer (OPTIMIZER_MODE = CHOOSE), and have not changed the default value of OPTIMIZER_GOAL, which the documentation says defaults to ALL_ROWS.
  2. All of the tables in the query have been ANALYZEd.
  3. We ran the two querys (the INSERT and the INSERT...SELECT...) with each of the following hints: RULE, CHOOSE, FIRST_ROWS, ALL_ROWS.
  4. We ran each query several times and in different orders and eliminated results that were obviously influenced by caching

These are the results:

Query                                                        SELECT
INSERT...SELECT...


No Hint (should use ALL_ROWS) 50 seconds 210 seconds
RULE                                                        210 seconds
210 seconds
CHOOSE                                                 50 seconds
210 seconds
ALL_ROWS                                             50 seconds
50 seconds
FIRST_ROWS                                         50 seconds
50 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US