Re: Tuning Advice
Date: Fri, 27 Apr 2018 17:54:54 +0000
Message-ID: <LO1P123MB09772BBA713FF199592E3E79A58D0_at_LO1P123MB0977.GBRP123.PROD.OUTLOOK.COM>
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
Hello Gurus,
Oracle 12.1.0.2
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
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com>
Sent: 27 April 2018 16:50
To: ORACLE-L
Subject: Tuning Advice
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 27 2018 - 19:54:54 CEST