Useful 12c new features for developers

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.

1. 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:
   
   Oracle PL/SQL

   Select Employee_id, First_name, Salary from (select Employee_id, First_name, Salary from Hr.Employees order by salary desc) where rownum <= 10; --or as a cooler version Select Employee_id, First_name, Salary from (select Employee_id, First_name, Salary, Row_number() Over(order by Salary desc) row_order from Hr.Employees) where row_order <= 10;

   1 2 3 4 5 6 7 8 9 10 11 12

   Select Employee_id, First_name, Salary from   (select Employee_id, First_name, Salary         from Hr.Employees         order by salary desc) where rownum <= 10;   --or as a cooler version   Select Employee_id, First_name, Salary from   (select Employee_id, First_name, Salary, Row_number() Over(order by Salary desc) row_order         from 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:
   
   Oracle PL/SQL

   Select Employee_id, First_name, Salary from Hr.Employees order by salary desc Fetch first 10 rows only; --also you can skip first X rows Select Employee_id, First_name, Salary from Hr.Employees order by salary desc offset 5 rows Fetch first 10 rows only;

   1 2 3 4 5 6 7 8 9 10 11

   Select Employee_id, First_name, Salary from   Hr.Employees order by salary desc Fetch first 10 rows only;   --also you can skip first X rows   Select Employee_id, First_name, Salary from   Hr.Employees order by salary desc offset 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:
   
   Oracle PL/SQL

   explain plan for Select Employee_id, First_name, Salary from Hr.Employees order by salary desc Fetch first 10 rows only; --explained select * 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)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

   explain plan for Select Employee_id, First_name, Salary from   Hr.Employees order by salary desc Fetch first 10 rows only;   --explained   select * 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)

2. 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:
   
   Oracle PL/SQL

   drop 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

   1 2 3 4 5 6 7 8 9

   drop 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.
3. Identity Column and Sequence Nextval as Default Column Value
   First, you can assign a sequence next value to a column default value.
   
   Oracle PL/SQL

   create 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 ---------- ---------- 1 2 second row

   1 2 3 4 5 6 7 8 9 10 11 12

   create 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 ---------- ----------          1          2 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.
   
   Oracle PL/SQL

   drop sequence seq; drop table dummy; create sequence seq; create table dummy(id number , mydata varchar2(10)); create or replace trigger tr_bi_dummy before insert on dummy for each row begin :new.id := seq.nextval; end; / insert into dummy (mydata) select 'test rows' from dual connect 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 dual connect by level < 100000; --99999 rows created. --Elapsed: 00:00:01.04

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   drop sequence seq; drop table dummy; create sequence seq; create table dummy(id number , mydata varchar2(10));   create or replace trigger tr_bi_dummy before insert on dummy for each row begin   :new.id := seq.nextval; end; /   insert into dummy (mydata)   select 'test rows' from dual   connect 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 dual   connect 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:

   Oracle PL/SQL

   create 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 data

   1 2 3 4 5 6 7

   create 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 data

   this is not magical of course, let’s check which objects we just created:

   Oracle PL/SQL

   select cast(object_name as varchar2(20)), object_id, created from user_objects order by created desc fetch first 2 rows only; OBJECT_NAM OBJECT_TYPE CREATED -------------------- ---------- --------- ISEQ$$_76120 SEQUENCE 25-FEB-19 DUMMY TABLE 25-FEB-19

   1 2 3 4 5 6 7

   select cast(object_name as varchar2(20)), object_id, created from user_objects order by created desc fetch first 2 rows only;   OBJECT_NAM           OBJECT_TYPE CREATED -------------------- ----------  --------- ISEQ$$_76120         SEQUENCE    25-FEB-19 DUMMY                TABLE       25-FEB-19

   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 PL/SQL

   select table_name, column_name, data_default from user_Tab_cols where table_name ='DUMMY'; TABLE_NAME COLUMN_NAME DATA_DEFAULT -------------------- -------------------- -------------------------------- DUMMY ID "MUSTAFA"."ISEQ$$_76120".nextval DUMMY MYDATA

   1 2 3 4 5 6 7

   select table_name, column_name, data_default from user_Tab_cols where table_name  ='DUMMY';   TABLE_NAME           COLUMN_NAME          DATA_DEFAULT -------------------- -------------------- -------------------------------- DUMMY                ID                   "MUSTAFA"."ISEQ$$_76120".nextval DUMMY                MYDATA

   Oracle just created a sequence and assign it as default value of that column.

4. 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.
   Oracle PL/SQL

   select sum(sf_round(data_object_id)) from tmp_objects; SUM(SF_ROUND(DATA_OBJECT_ID)) ----------------------------- 164146565 Elapsed: 00:00:01.14 /***********************************************************/ with function my_round(p number) return number as begin return round(p,4); end; select sum(my_round(data_object_id)) from tmp_objects; / Elapsed: 00:00:00.30

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   select sum(sf_round(data_object_id)) from tmp_objects; SUM(SF_ROUND(DATA_OBJECT_ID)) -----------------------------                     164146565   Elapsed: 00:00:01.14   /***********************************************************/   with function my_round(p number) return number as begin   return 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.