Oracle 12c New Features For Developers

As with every new release, there are hundreds of Oracle 12c new features so we won't (can't) look at them all. What we will be doing is selecting some of what we feel are the moat important or most significant changes and providing a more in-depth analysis of them.

Of course, the most significant new feature in Oracle 12c is something you will be hearing a lot about in the future if you haven't already done so - the new multi-tenant architecture. This enables a database to have one or more child databases that can all be managed in one go and enables database administrators to clone databases quickly and easily.

However the impact of this on developers is almost zero - as a developer yopu won't know or care whether the database you are using is a stand alone database or a container or pluggable database - but it is worth knowing as background information. For more information see the Oracle documentation - in particular the Oracle Concepts manual.

Top N Queries

The first Oracle 12c new feature we'd like to look at is a simplification of the "top n" query feature. This is a way of limiting the number of rows returned to the first n meeting the relevant criteria. For example you might wish to retireve the top 10 highest paid employees or the top 10 customers with the highest purchases. You might want to do this if you're presenting results via a browser and need to display results one page at a time.

Before Oracle 12c there were several ways of doing this. You could use a sub query like this:

select * from (
    select employee_id
          ,first_name||' '||last_name emp_name
          ,salary
    from employees
    order by salary desc )
where rownum < 11

Or you could use the analytic functions rank, dense rank, and row_number.

MySQL however has a limit function which makes the query much simpler.

select * from employees order by column_1 limit 0,10

For this Oracle 12c new feature, Oracle have taken a leaf out of the MySQL syntax book dispensing with the need for a sub query and introducing new syntax.

With Oracle 12c to retrieve the top 10 highest earning employees our query is now:

    select employee_id
          ,first_name||' '||last_name emp_name
          ,salary
    from employees
    order by salary desc
      fetch first 10 rows only

This is much simpler but returns the same results as the original query (which is not surprising because behind the scenes Oracle transforms this into exactly the same query we had before).

EMPLOYEE_ID  EMP_NAME          SALARY
100         "Steven King"       24000
101         "Neena Kochhar"     17000
102         "Lex De Haan"       17000
145         "John Russell"      14000
146         "Karen Partners"    13500
201         "Michael Hartstein" 13000
108         "Nancy Greenberg"   12008
205         "Shelley Higgins"   12008
147         "Alberto Errazuriz" 12000
168         "Lisa Ozer"         11500

If we replace the word "only" with the phrase "with ties" we will return more than the specified number of rows if the nth value has 2 or more rows with the same value. In our data, Neena Kochhar and Lex De Haan have the same salary, so if we change our query to use "with ties" and retrieve just the top 2 values, we'll see 3 rows returned because there are 2 employees with the 2nd highest salary.

select employee_id
      ,first_name||' '||last_name emp_name
      ,salary
from employees
order by salary desc
fetch first 2 rows with ties

EMPLOYEE_ID  EMP_NAME          SALARY
100         "Steven King"       24000
101         "Neena Kochhar"     17000
102         "Lex De Haan"       17000

That gives us the first n rows but what do we do if we want the next 10?


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.


We have to use a variation of this Oracle 12c new feature - the offset clause:

select employee_id
      ,first_name||' '||last_name emp_name
      ,salary
from employees
order by salary desc
offse 10 rows
fetch next 10 rows only

Which gives the following results:

EMPLOYEE_ID    EMP_NAME        SALARY
114          "Den Raphaely"     11000
174          "Ellen Abel"       11000
148          "Gerald Cambrault" 11000
149          "Eleni Zlotkey"    10500
162          "Clara Vishney"    10500
150          "Peter Tucker"     10000
204          "Hermann Baer"     10000
169          "Harrison Bloom"   10000
156          "Janette King"     10000
170          "Tayler Fox"       9600

Instead of specifying a fixed number of rows to fetch we can specify a percentage but unfirtunately we can't specify a percentage for the offset, so the the following is NOT valid

select employee_id
,first_name||' '||last_name emp_name
,salary
from employees
order by salary desc
offset 2 percent rows fetch next 2 percent rows only

but this is:

select employee_id
      ,first_name||' '||last_name emp_name
      ,salary
from employees
order by salary desc
offset 10 rows fetch next 2 percent rows only


That's the first Oracle 12c new feature in our series. As valuable as tehse articles are, the quickest way to learn Oracle os to take a formal training course. We partner with Gogo training to provide high quality, online, self-paced training courses taught by industry experts to help you gain the kowledge, skills and experience you need to advance your career.

Go to GogoTraining to see the online training course available and enter "ASKTHEORACLE" as the coupon code when you check out to get a 5% discount.

Also check out the Oracle, SQL and PL/SQL tutorials on our site. If you can't find what you're looking for try the search facility and if you still can't find the answer to your question, ask us.