Enhancements in Oracle 12c Database Part4

ROW limiting for Top-N result queries

There are various indirect approaches/methods exist to fetch Top-N query results for top/bottom rows in the previous releases. In 12c, retrieving Top-N query results for top/bottom rows simplified and become straight forward with the new FETCH FIRST|NEXT|PERCENT clauses.

In order to retrieve top 10 salaries from EMP table, use the following new SQL statement:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY;

The following example limits the fetch to 10 per cent from the top salaries in the EMP table:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 PERCENT ROWS ONLY;

The following example offsets the first 5 rows and will display the next 5 rows from the table:

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

All these limits can be very well used within the PL/SQL block too.

BEGIN

SELECT sal BULK COLLECT INTO sal_v FROM EMP FETCH FIRST 100 ROWS ONLY;

END;

Top-N Restrictions:

• If you have a SELECT statement with FOR UPDATE, you can’t use it.

• If the query of the Materialized Views has this clause, then you can’t do an incremental refresh of

that MV

DEFAULT Values for Columns on Explicit NULL Insertion

The DEFAULT definition of a column can be extended to have the DEFAULT being applied for explicit 

NULL insertion. The DEFAULT clause has a new ON NULL clause, which instructs the database to assign a specified default column value when an INSERT statement attempts to assign a value that evaluates to NULL.

SQL> create table t5 (col1 number, col2 number default on null 0);

Table created.

SQL> desc t5

Name Null? Type

------ --------- -------- --

COL1 NUMBER

COL2 NOT NULL NUMBER

SQL> insert into t5 values (1, null);

SQL> insert into t5 values (2,2);

SQL> select * from t5;

COL1 COL2

-------- ---------

1 0

2 2 

<<<Previous