Tuesday, March 8, 2011

Edition based redefinition

Oracle 11g brought in a new feature called Edition based redefinition (EBS) basically aimed at reducing the planned downtime during application releases/upgrades etc. In this changing and dynamic world, application also undergoes numerous enhancements; it’s in these situations that one cannot afford to take a downtime and looks for options to minimize or eliminate them. While the application code and the software provide some options, the Oracle database engine had no such option.  

Till recently, Oracle database had only few options – such as online index rebuild and table redefinition to address planned downtime [Also has Workspace manager; Thanks for pointing out Gary Myers]. However, we all know Oracle’s capability to address unplanned downtimes with features such as – Real Applications Cluster (RAC), Physical / logical standby database, Streams etc.

With 11gR2, Oracle claims to have addressed the gap with edition based redefinition.  Oracle now supports multiple versions (editions) of a given object. So now, the objects are called as “editioned object” or “non-editioned objects” based on whether an object can have multiple versions or not. While one version of the object is used by the live application, the other version can be used to carry out changes and tested before making it permanent. However, the editionable feature is available for the following database objects ->
  • Synonym
  • View
  • Procedure
  • Function
  • Package and Package Body pe
  • Trigger
  • Type and Type Body
  • Library
The all-important and most used object – table and indexes are missing from the above list. So if you look at the list, you will feel that the versioning of code is now extended to database engine. Only view (though that’s also a piece of SQL code) and synonyms have been added. However, one can work around table by creating views and making them editionable but again only simple views without the use of functions etc. can be editioned. As a change, the developer needs to make his/her code work against editionable objects.

The other important thing is data within the table can’t be versioned; a lot of changes happen to be DML in nature so this is another important thing missing though there are workaround.

So, in my opinion this feature is useful in the following scenario =>
  • If you make changes to procedures, function etc.
  • Changes are done to physical structure of tables rather than data
Otherwise I feel this is not as useful yet. Also it’s a relatively a new feature and yet to mature. I'm sure future releases of Oracle will bring in some enhancements.
Enhanced by Zemanta


Aman Sharma said...

Very nicely explained Siba. I think if you can show a small demo of it, it would an icing on the cake.


Gary Myers said...

Workspace Manager has been present in the database since 8i for having different versions of data.


My Thoughts said...

Thanks Aman and Gary for the feedback.

@Gary, Thanks for correcting. I overlooked workspace manager. It's apt for data versioning. I'll add correction to my post and mention about Workspace manager.

Patrick said...

Hi Siba,

The part of this post that bothers me the most is:
The all-important and most used object – table and indexes are missing from the above list.
That's the whole point of EBR. The data cannot be Edition Based. Therefore you need to direct you programming against (simple) views instead of directly to the tables, which is a good idea in any environment.
Using triggers you can make sure the data gets displayed correctly in every edition you have enabled.
Just my 2 cents.

kindest regards,
Patrick Barel

My Thoughts said...

Thanks Patrick for your thoughts.