RE: GTT and Undo and Redo

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 28 Feb 2014 09:44:58 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE0864_at_exmbx05.thus.corp>

Should have pointed out that the answers applied to your 11.2 question.

Things change in 12c where you can enable "temp undo" - which means the undo for operations on GTTs goes to the database default temporary tablespace.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: Jonathan Lewis
Sent: 28 February 2014 09:42
To: Hemant-K.Chitale_at_sc.com; ORACLE-L Subject: RE: GTT and Undo and Redo

Note inline

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chitale, Hemant K [Hemant-K.Chitale_at_sc.com] Sent: 28 February 2014 09:14
To: ORACLE-L
Subject: GTT and Undo and Redo

GTT in 11.2

Quick question or clarification :

No Redo is generated for an Insert into a GTT

>> Correct, if you ignore the redo generated to describe the undo

There is Undo generated

>> Correct

Questions :

If we do a Direct Path Insert / Parallel Insert into the GTT can we avoid/reduce the Undo as well (and the Redo for the Undo) ?

>> Yes, but you'll have to commit, so the GTT will have to be "on commit preserve rows"

If there is an Index on the GTT, undo is generated for the index as well – what about a Direct Path Insert ?

>> Still generated, but for direct path insert (generically) Oracle optimizes the generation of redo and undo,

>> so the undo (and redo for undo) for the index could be reduced.

>> The scale of the difference will depend on whether or not the GTT already holds data, and how much

How does this work in a FORCE_LOGGING database ? Does it force redo for all direct path operations against a GTT and Index on GTT ? My Test environment doesn’t have FORCE_LOGGING (and I am not the DBA) so I can’t test this.

>> The temporary tablespace is not logged even when database force_logging is enabled

Hemant K Chitale

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 28 2014 - 10:44:58 CET