Hello Everyone,
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:
123456789101112Select Employee_id, First_name, Salaryfrom (select Employee_id, First_name, Salaryfrom Hr.Employeesorder by salary desc)where rownum <= 10;--or as a cooler versionSelect Employee_id, First_name, Salaryfrom (select Employee_id, First_name, Salary, Row_number() Over(order by Salary desc) row_orderfrom Hr.Employees)where row_order <= 10;
with 12c we can finally can get rid of that subquery with new FETCH clause. Equivalent version of sql’s above in 12c is:
1234567891011Select Employee_id, First_name, Salaryfrom Hr.Employeesorder by salary descFetch first 10 rows only;--also you can skip first X rowsSelect Employee_id, First_name, Salaryfrom Hr.Employeesorder by salary descoffset 5 rows Fetch first 10 rows only;
if you check execution plan for this new query you will see that it uses Row_Number() analytic function to fetch rows:
1234567891011121314151617181920212223242526explain plan forSelect Employee_id, First_name, Salaryfrom Hr.Employeesorder by salary descFetch first 10 rows only;--explainedselect * from table(dbms_xplan.display());--output:--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 640 | 4 (25)| 00:00:01 ||* 1 | VIEW | | 10 | 640 | 4 (25)| 00:00:01 ||* 2 | WINDOW SORT PUSHED RANK| | 107 | 1605 | 4 (25)| 00:00:01 || 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC)<=10) - 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:
123456789drop table dummy;create table dummy(id number, mydata varchar2(10) default on null 'Thor');insert into dummy (id,mydata) values (1, null);select * from dummy;ID MYDATA---------- ----------1 Thor
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.
123456789101112create sequence seq;create table dummy(id number default seq.nextval, mydata varchar2(10));insert into dummy (mydata) values ( null);insert into dummy (mydata) values ( 'second row');select * from dummy;ID MYDATA---------- ----------12 second row
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.
12345678910111213141516171819202122232425262728293031323334drop sequence seq;drop table dummy;create sequence seq;create table dummy(id number , mydata varchar2(10));create or replace trigger tr_bi_dummybefore insert on dummyfor each rowbegin:new.id := seq.nextval;end;/insert into dummy (mydata)select 'test rows' from dualconnect by level < 100000;--99999 rows created.--Elapsed: 00:00:07.69/***********************************************************************/drop sequence seq;drop table dummy;create sequence seq;create table dummy(id number default seq.nextval, mydata varchar2(10));insert into dummy (mydata)select 'test rows' from dualconnect by level < 100000;--99999 rows created.--Elapsed: 00:00:01.04
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:
1234567create table dummy(id number generated always as identity , mydata varchar2(10));insert into dummy (mydata) values ('test data');select * from dummy;ID MYDATA---------- ----------1 test datathis is not magical of course, let’s check which objects we just created:
1234567select cast(object_name as varchar2(20)), object_id, created from user_objects order by created descfetch first 2 rows only;OBJECT_NAM OBJECT_TYPE CREATED-------------------- ---------- ---------ISEQ$$_76120 SEQUENCE 25-FEB-19DUMMY TABLE 25-FEB-19as 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:
1234567select table_name, column_name, data_defaultfrom user_Tab_cols where table_name ='DUMMY';TABLE_NAME COLUMN_NAME DATA_DEFAULT-------------------- -------------------- --------------------------------DUMMY ID "MUSTAFA"."ISEQ$$_76120".nextvalDUMMY MYDATAOracle 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.1234567891011121314151617select sum(sf_round(data_object_id)) from tmp_objects;SUM(SF_ROUND(DATA_OBJECT_ID))-----------------------------164146565Elapsed: 00:00:01.14/***********************************************************/with function my_round(p number) return number asbeginreturn round(p,4);end;select sum(my_round(data_object_id)) from tmp_objects;/Elapsed: 00:00:00.30
There are many different new features of course but those are my favorite ones. Hope you like it.
See you soon.