Re: Tuning Advice

From: Jonathan Lewis <>
Date: Fri, 27 Apr 2018 17:58:52 +0000
Message-ID: <LO1P123MB09774EA00D83F69B3D6DAC8CA58D0_at_LO1P123MB0977.GBRP123.PROD.OUTLOOK.COM>

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.

Jonathan Lewis

From: <> on behalf of Sheehan, Jeremy <> Sent: 27 April 2018 16:50
Subject: Tuning Advice

Hello Gurus,

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!


Received on Fri Apr 27 2018 - 19:58:52 CEST

Original text of this message