On July 1st, 2013 Oracle released the Oracle 12c Database, the first database designed for the cloud.
In this blog post we'll discuss some cool, new features for SQL and PL/SQL developers in the Oracle 12c Database
Introduction
Some figures
Important new release:
Overview
The core aims of Oracle Database 12c are:
One of the main new features Oracle offers to accomplish this is the “Multitenant architecture with pluggable dabases”.
As you can see in the picture it will be possible, for example, to combine the 3 separate databases (ERP, CRM and DW) into 1 container database (CDB) with 3 pluggable databases (PDB).
Memory, processes and metadata are maintained on 1 place (CDB) for all 3 PDB's.
The PDB's only contain the application data and the specific data dictionaries for their own system.
In consequence, the PDB's need less hardware resources and are better scalable. And DBA work like upgrading, patching and migrations should be much simpler and thus faster.
For example: One patch which is executed on the CDB is also immediately installed on all PDB's in the container.
This feature is especially important for large companies with a lot of databases who want to simplify their maintanance.
New features
The complete list of new features can be found in this list.
We'll discuss some features which will come in handy for SQL and PL/SQL developers:
It's possible to use a PL/SQL function in a SQL WITH clause.
In the example below a function is set in the WITH clause to add a commission of 10% to the salary:
WITH
FUNCTION commission(sal number)
RETURN number
IS
BEGIN
return sal * 1.1;
END;
SELECT ename, sal, commission(sal)
FROMemp;
Default values for columns can directly refer to Oracle sequences. Valid entries are sequence.CURRVAL and sequence.NEXTVAL.
Providing the functionality to directly refer to a sequence as a default value expression simplifies code development.
Example:
Create table my_table
(
id number default my_sequence.nextval primary key
);
Instead of using a default sequence, the column can also be created with the keyword “IDENTITY”.
This provides a standards based approach to the declaration of automatically incrementing columns, which simplifies application development and makes the migration of DDL to Oracle simpler.
Example:
create table my_table
(
id number generated as identity
);
This standard ensures an auto-increment of the column value when adding new data to the table.
The length of the VARCHAR2, NVARCHAR2 and RAW datatypes has been enlarged from 4000 to 32767 bytes.
So, a LOB type is less quickly necessarry to use in a table and this could have a significant impact on the memory and disk usage of the database.
It is possible for the optimizer to miscalculate some estimations during initial plan generation. Adaptive query optimization allows these miscalculations to be corrected in one of the following two ways:
Adaptive query optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics.
The UNION and UNION ALL statements can run several branches concurrently instead of working on them one-by-one.
Executing multiple branches of UNION and UNION ALL statements in parallel speeds up processing time and leads to better resource utilization.
In-Database Archiving allows users and applications to set the archive state for individual rows. Rows that have been marked as archived will not be visible unless the session is enabled to see archived data.
With In-Database Archiving, more data can be stored in production databases for a longer period of time without compromising application performance. In addition, archived data can be aggressively compressed to help improve query and backup performance. Updates to archived data can be deferred during application upgrades, greatly improving the performance of upgrades.
In this release, SecureFiles is now the default for LOB storage when the compatible initialization parameter is set to 12.1 or higher.
The SecureFiles feature provides optimal performance for storing unstructured data in the database. Making SecureFiles the default for unstructured data helps ensure that the database is delivering the best performance possible when managing unstructured data.
The property of whether a column is visible can be controlled by the user. Invisible columns are not seen unless specified explicitly in the SELECT list. Any generic access of a table (such as a SELECT * FROMtable or a DESCRIBE) will not show invisible columns.
This can benefit the safety of the database.
Example:
create table users
(
id number,
name varchar2(100),
email varchar2(255),
password varchar2(100) INVISIBLE
);
A describe of this table would return the following:
SQL>desc users
Name Null? Type
------------ ------------- -------------------------
ID NUMBER
NAME VARCHAR2(100)
EMAIL VARCHAR2(255)
Multiple indexes can be created on the same set of columns as long as some characteristic is different. Qualifying characteristics are:
Providing the capability to create multiple indexes on the same set of columns enables transparent and seamless application migrations without the need to drop an existing index and re-create it with different attributes.
For example:
The command TRUNCATE TABLE my_table CASCADE will empty the complete table my_table, but also all optionally child-records.
The FETCH FIRST and OFFSET clauses provide native SQL language support to limit the number of rows returned and to specify a starting row for the return set.
Many queries need to limit the number of rows returned or offset the starting row of the results. For example, top-N queries sort their result set and then return only the first n rows. FETCH FIRST and OFFSET simplify syntax and comply with the ANSI SQL standard.
For example: Query the top 5 highest earning employees:
SELECT ename, sal
FROM emp
ORDER BY sal DESC
FETCHFIRST5 ROWSONLY;
Hope this helps!
GDD/Juvo
Resources:
