Oracle Open World 2011: Oracle Database 11g Features for Developers by Connor McDonald

Yesterday I had the pleasure of attending a session by Connor McDonald. I heard a lot about him, his presentation style, and I even did a book review back in 2005. Everybody I talk to who attended a session by Connor – ever – is always very positive on his presentation style. And it is very impressive, humor, technical knowledge, all the ingredients are there to keep you focused on the content. The opening slide, he put on as you walked in the room invited you to move forward as the presentation has code sample in a fontsize which he showed on the slide. The room filled up, and it was a large room. There was another benefit of being in the front of the room, apart from being able to read the slides, he handed out chocolates – starting from the front. Talking to Connor later in the evening, he told me it takes months to prepare for a presentation, and it really shows. If you get a chance to see Connor do a presentation, attend it and make sure to arrive early! Part of the presentation was on Edition Based Redefinition, and because I presented on that subject before I believed there was a flaw in his presentation. But there was not, I was mistaken. The rest of this blogpost shows you where I was mistaken, and Connor was absolutely right.

When creating an editioning view, I believed it was “required” to name the columns in your view instead of using the wildcard “*” to select the columns. Shielding the editioning view from table alterations. So let’s start with an edition enabled user to setup our table.

As you can see in the above code snippet, I run this test on Oracle Database 11g Express Edition. The tablename I picked up from Connor’s session. Naming your table something freakish will (hopefully) stop developers from using the table directly in their code. I like that naming convention. Let’s continue with two editions

I believed, wrongly, that the wildcard “*” would be dynamic, meaning that if the table definition would change both editioning view would include the new column as well. This is not the case.The editioning views don’t even get invalidated…

As you can see in the output above, the actual column names are in the metadata, shielding changes to the editioning views when the table definition changes. Connor was absolutely right about the mechanics of Edition Based Redefinition. Does this mean you should use the wildcard “*”in your editioning views? I believe not, I believe you should explicitly name the columns that you want to include in a specific edition. This way it is more clear to the developers, and DBA alike. Rerunning the scripts might all of sudden include columns in an edition which weren’t suppose to be there. When you explicitly name your columns, you won’t have this problem.