Oracle 12c New Features for Developers

One of the seminars I gave in Oracle Week 2013 was “Oracle Database 12c New Features for Developers”. I covered quite a lot of features (well, it was a full day seminar), and I tried to present them in a rational order, by dividing them to categories and talking about one category at a time. Of course, there are features that fall into more than one category, so I had to choose where to talk about such features deeply and where to only mention them.

Following is the complete list of features I covered in the seminar, with a very very short description for each one of them. I plan to write dedicated posts about some of them. In the meantime, I’m adding links to some good references.

The presentation is available here.

 

For starters (probably not the most important new feature 😉 )

• Last login

C:\Users\orenn>sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 9 08:24:09 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: hr
Enter password:
Last Successful login time: Fri Nov 08 2013 17:26:27 +02:00

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Schema-level new features

• Invisible columns: Invisible columns are invisible, unless explicitly specified (for example, they will not appear in the result set of “select * from table”) [a post by Tom Kyte]
• Column defaults [an article by Tim Hall]
  • Sequence: a column can be associated with a user-defined sequence
  • On null: a default can be defined for explicit Null values
  • Identity: a column can be defined as “identity”, which implicitly associates it with a system-generated sequence
  • New optional column: adding an optional column with default is a meta-data only operation
• Extended strings: the maximum length of RAW and N/VARCHAR2 columns can be extended to 32KB [an article by Tim Hall]
• Multiple indexes on the same column list: it is possible to define several indexes on the same column list, as long as they are different somehow (unique vs. non-unique, b*tree vs. bitmap, etc.) and only one of them is visible [a post by Richard Foote]
• Session level sequences: non-persistent sequences, return a range of numbers which is unique within a session
• Temporary undo: undo for temporary tables can be stored in the temporary tablespace

Optimizer new features

• Histograms: new types of histograms – “top frequency” and “hybrid” – can give much better information than the legacy “frequency” and “height balanced” ones [posts by Jonathan Lewis: part 1, part 2, part 3 (with a very clear example of hybrid histograms)]
• Online statistics gathering: table and column (but not index and histogram) statistics are gathered automatically during direct path load into empty tables/partitions
• Dynamic statistics: a new level of dynamic statistics (formerly known as dynamic sampling)  – 11 – enables the optimizer to automatically decide to use dynamic statistics for any SQL statement, and the results are persisted in the cache
• Global temporary tables: statistics gathered on global temporary tables are kept and used either in session-level (the default) or shared across sessions (as in pre-12c versions)
• Concurrent execution of UNION/ALL: different branches of UNION operations may be executed in parallel (if at least one of the branches is considered being processed in parallel)

PL/SQL new features

• White lists: a new clause – ACCESSIBLE BY – can be added to packages, procedures, functions and types to specify which objects are able to reference the PL/SQL object directly [an article by Tim Hall]
• Invoker’s rights enhancements
  • Granting roles to program units: roles can be granted to program units [a post by Tom Kyte]
  • BEQUEATH: a new clause in view definition – BEQUEATH – specifies whether functions referenced in the view are executed using the view invoker’s rights or the view definer’s rights
  • INHERIT PRIVILEGES: when executing an invoker’s rights procedure the owner gets access to the invoker’s privileges; in 12c the owner must be granted INHERIT PRIVILEGES on the invoker to achieve this
  • Function result cache: invoker’s rights functions can be RESULT_CACHE-ed too (not only definer’s rights functions)
• SQL text expansion: DBMS_UTILITY.EXPAND_SQL_TEXT reveals the actual SQL executed for a given query [a post by Tom Kyte]
• Introspection: the new package UTL_CALL_STACK gives a modeled access to call stacks and error backtrace, and a fine-grained resolution within the program unit [a post by Tom Kyte]
• Binding: it is possible to bind PL/SQL-only data types to SQL statements
• SELECT from collections: it is possible to select from package-level collections

Easier migration to Oracle

• Implicit result sets: it is possible to return result sets not only explicitly through cursor variables, but also implicitly [an article by Tim Hall]
• SQL translation framework: SQL Translation Framework translates SQL statements of a client program from a non-Oracle dialect into the Oracle dialect (and can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue) [a post by Kerry Osborne]

SQL new features

• Row limiting: a new clause of the SELECT statement allows to fetch a specific chunk of X rows or X% of rows [an article by Tim Hall]
• PL/SQL in the WITH clause: the WITH clause can include not only subquery factoring but also PL/SQL declarations of functions that can be used in the query (and procedures that can be used in the functions) [an article by Tim Hall]
• Pattern matching: the SELECT syntax allows enhanced analysis of row sequences
• TRUNCATE CASCADE: it is possible to truncate the parent table of an enabled foreign key constraint, and to truncate the parent table of a reference-partitioned table (assuming the foreign key is defined with ON DELETE CASCADE)
• New join syntax: now supported – CROSS APPLY, OUTER APPLY, LATERAL inline views, multi-table “left outer join” with Oracle syntax (+)
• Online operations: more operations can be executed online (DROP INDEX, DROP CONSTRAINT, SET UNUSED COLUMN, ALTER INDEX UNUSABLE, ALTER INDEX VISIBLE|INVISIBLE, ALTER TABLE MOVE (SUB)PARTITION)

Utilities new features

• Data Pump enhancements: added options to disable redo logging in impdp, to change table compression in impdp, to export views as tables in expdp, to add timestamps to the log file
• SQL*Loader express mode: SQL*Loader can be executed with no control file and with many defaults, and it generates a log file for future use including control file, CREATE EXTERNAL TABLE statement and a corresponding INSERT statement

Transaction Guard

• Transaction Guard: a generic tool for applications to use for at-most-once execution in case of outages
  

Information lifecycle management

• Temporal validity: tables can have pairs of columns defining “time periods”, and data can be selected by a specific valid time (or range)
• In-database archiving: tables can be defined as ROW ARCHIVAL, which implicitly adds a hidden column to the table, holding an archiving (“logical deletion”) state;  “archived” rows become invisible  (or visible, when we want)
• Heat map: the Heat Map feature provides data access tracking (last read time, last update time, last full table scan, last index scan)
• Automatic data optimization: policies can be defined to automatically move data between storage tiers and to automatically compress data [an Oracle Magazine article by Arup Nanda]

Partitioning new features

• Partitioning schemes: the combination of reference partitioning and interval partitioning is possible
• Asynchronous global index maintenance: DROP/TRUNCATE PARTITION of a table with global indexes can be now a fast operation while keeping the global indexes usable; the “orphaned” index entries are deleted asynchronously [posts by Richard Foote: part 1, part 2, part 3]
• TRUNCATE/EXCHANGE CASCADE: it is possible to TRUNCATE/EXCHNAGE partitions of the parent table of a reference-partitioned table (assuming the foreign key is defined with ON DELETE CASCADE)
• Multiple (sub)partitions in single DDL operations: it is possible to handle multiple partitions in a single statement (depending on the partitioning scheme) – ADD | DROP | MERGE | SPLIT | TRUNCATE
• Partial indexes: it is possible to create an index on a subset of partitions [posts by Richard Foote: part 1, part 2]