Database DataWarehousing (MOSC)

MOSC Banner

Partition truncate with deferred invalidation, any side effects?

edited Sep 1, 2019 10:56AM in Database DataWarehousing (MOSC) 3 commentsAnswered

Hello community,

I'm planning to make a presentation on truncate in Oracle community, in which I'm going to introduce some of the new features introduced in 12c, specifically "deferred invalidation" option. I would like to share my small findings here.

Traditionally, truncating a partition will invalidate all the associated cursors in library cache, and eventually require hard parses on next execution.

With the deferred invalidation option like the following example, it is now possible not to make that happen anymore. Throughout my quick observation, I found cursors with full table scan or index range scan with local indexes will not be invalidated with truncating a partition.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center