I just upgraded one of my customers database to 12c R2 and it has really nice features so I wanted to share some of them for developers.
- Top N rows:
this is a late feature but finally arrived. Before 12c if we want to first N rows from an ordered dataset we would have to write something like this:
with 12c we can finally can get rid of that subquery with new FETCH clause. Equivalent version of sql’s above in 12c is:
if you check execution plan for this new query you will see that it uses Row_Number() analytic function to fetch rows:
- Table Column Default On Null:
Before 12c we can assign a default value to a column but this will work only if user does not use that column in insert script. Also if user sends NULL as value, your default value won’t be use again. after 12c we are able to define a value even user sends NULL in insert script:
if you are someone like me who almost hates NULL values, this is a great feature.
- Identity Column and Sequence Nextval as Default Column Value
First, you can assign a sequence next value to a column default value.
so this is not just a shortcut for writing insert statements. many companies (unfortunately) use triggers to assign id column value which is a complete disaster!
Rule of Thumb: Triggers are evil! if you have option not to use trigger, do not use trigger!
Let’s check performance of sequence default value and using trigger for that job.
so it is around 7 times faster then trigger! Yupp!
Also you can create identity columns anymore and don’t have to deal with sequences:
this is not magical of course, let’s check which objects we just created:
as you can see there is a new sequence called ISEQ$$_76120. This is created for our identity column. In the end, identity data is generated from a sequence. check default data for identity column:
Oracle just created a sequence and assign it as default value of that column.
- Creating PLSql Functions in SQL:
This is a great feature. It is probably one of the worst thing to use a plsql function in Sql! this will cause context switch and decrease your Sql Performance.
There are many different new features of course but those are my favorite ones. Hope you like it.
See you soon.