Jonathan touches on another important question.
How much data is in this table you're inserting into AND has the table been
having "DELETES" on it? You might could try a simple "INSERT /*+ append */
hint if the problem is looking for free space in the table below the HWM.
Chris
On Fri, Apr 27, 2018 at 12:54 PM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> wrote:
The commonest resolution of that type of problem is that "select" may be
running under first_rows(n) optimization which changes to all_rows as soon
as you change the query to "insert as select"; alternatively the plan for a
distributed select can do some optimizations that "insert as distributed
select" can't.
In your case, though, you say a CTAS is just as fast as a select - and in
both the above CTAS would have the same problem as "insert as select".
This isn't just a case of your insert maintaining indexes while your CTAS
doesn't have any indexes ?
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
Sent: 27 April 2018 16:50
To: ORACLE-L
Subject: Tuning Advice
Hello Gurus,
Oracle 12.1.0.2
AIX 7.1
I have a query that runs fairly well. Takes about 8 minutes to run,
nothing terrible about it (no huge FTS, joins seem to be in place
correctly). When I try to do an insert into TABLE as SELECT, it picks a
completely different execution plan and never finishes. The really odd
thing is that it works great when using CTAS or initial creation of a MV.
Any suggestion on how I can have the optimizer not use a specific
execution plan or any session level parameters that would have it use a
different execution plan?
Thanks in advance!
Jeremy
--
//www.freelists.org/webpage/oracle-l
