Another ought about select vs, insert as select, vs. CTAS.
Even in the absence of indexes the middle one will generate a lot of redo, but
if the database is in noarchivelog mode the CTAS will be a nologging operation.
It's possible that could make some difference to run time.
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