Author: Oren Nakdimon

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]

Lecture on EBR

In the next meeting of Oracle and ilOUG DBA Forum, on December 10th, I will talk about Edition-Based Redefinition, a powerful and fascinating feature of Oracle (added in version 11.2), that enables application upgrades with zero downtime, while the application is actively used and operational.
I will explain how to use it, give some tips from my hands-on experience with EBR, and present examples.

External Tables

This is a feature I use a lot…

An external table in Oracle is a table that its structure is defined in the data dictionary, but its content is taken from a standalone file, and is not stored inside the database. This feature is very useful as part of an ETL process, where you need to load the content of some files into the database.

The external file must be located in the file system of the database server machine (or at least in a directory that it can access). This is the major drawback I see in this feature with respect to its predecessor, SQL*Loader (which loads files located at the client machine). But the advantages are significantly superior to the disadvantages.

The definition of the external table describes how to locate, parse and process the file: Continue reading “External Tables”

Literals

It is possible to specify a date value as a literal, like that:

DATE '2013-08-29'

This instantiates a date constant, which is equivalent to the result of

to_date('29/08/2013','dd/mm/yyyy')

The date format in this case is always yyyy-mm-dd, and is not influenced by any parameter (the default format of the to_date function is defined by the NLS_DATE_FORMAT parameter).

For example, to find all the employees that were hired during August 2013 we can use the following query:

select * 
from employees 
where hire_date >= date'2013-08-01' 
  and hire_date < date'2013-09-01';

To specify a value that contain also a time part as a literal, you can use:

TIMESTAMP '2013-08-29 13:45:00'

The format is always yyyy-mm-dd hh24:mi:ss (fractions of seconds may also be specified).

The MERGE statement

The SQL statement MERGE (available from version 9i) provides the functionality of “INSERT or UPDATE” in a single statement.

The basic syntax is:

MERGE INTO <target>
USING <source>
ON (<join conditions>)
WHEN MATCHED THEN UPDATE <SET clause>
WHEN NOT MATCHED THEN INSERT (<column list>) VALUES (<expression list>);

where:

  • <target> is the table that we’d like to insert new records into and/or update existing records in
  • <source> is usually some SQL query (it may also be simply a table)
  • <join conditions> specify the relationship between the source and the target – these conditions determine whether a row from the source exists in the target or not

Each row from the source is looked up in the target table, using the conditions specified in the ON clause. If the row exists in the target table, then the WHEN MATCHED clause is executed – the target row is updated. If the row does not exist in the target table, then the WHEN NOT MATCHED clause is executed – a new row is inserted into the target table.

Let’s take an example. Continue reading “The MERGE statement”

Course Addendum

I lectured about Oracle SQL and PL/SQL in a course lately. As always, some issues that had not been part of the planned scope were raised and discussed, so I thought to write short posts about some of them, for the benefit of the participants and everyone else that may find it relevant.

These issues are:

Lateral Inline Views

Lateral Inline Views – an official feature of Oracle 12c (was hidden/unsupported/undocumented in 11g) – lets you refer within an inline view to tables that appear to its left in the FROM clause.

Let’s take an example (a simplification of a real-life use case I handled recently). We have a SCHEDULES table, including for each repeatable task the time range in which it should be executed, and the frequency:

create table schedules (
  name            varchar2(100) not null primary key,
  start_time      date,
  end_time        date, 
  repeat_interval interval day to second
);

Let’s fill it with 3 records: Continue reading “Lateral Inline Views”

The FIRST and LAST Aggregate Functions

One of my favorite aggregate functions in Oracle is FIRST (and of course its counterpart, LAST; ok, so two of my favorite aggregate functions).

The very common aggregate functions MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.

The FIRST and LAST functions extend this ability, and allow you to sort a group of rows by one column, but return the value of another column from the first or last row in the sorted group. Continue reading “The FIRST and LAST Aggregate Functions”