OracleSQL

Oracle 12c new features (Part – 2)

We have covered Top N queries and Invisible columns in Oracle 12c New Features (Part – 1)

In this article, we will learn:

  • Session-Level Sequences
  • Cascading Truncate
  • DDL Logging
  • Identity Columns
  • Multiple Index

3. Session-Level Sequences:
In Oracle 12c the CREATE SEQUENCE has a new clause to specify the scope of the sequence – either GLOBAL the default, or SESSION. For example CREATE SEQUENCE MySequence SESSION; .

Session-level sequences are designed to be used with global temporary tables and generate values that are unique only within the session. Also, as soon as the session ends, the state of the sequence is lost so a new session would generate values starting from the initial value defined for the sequence again.

4. Cascading Truncate:

Earlier we already know about cascading deletes – when the parent record is deleted any child records are also deleted. This is enabled by specifying the clause ON DELETE CASCADE when defining the foreign key constraints.Without the ON DELETE CASCADE clause we would get the errorORA-02292: integrity constraint (HR.FK_EMP_DEPT) violated – child record found.

If we attempt to truncate the table we also get an error message – ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

However in Oracle 12c we can add the clause CASCADE to the TRUNCATE statement like below:
SQL> TRUNCATE TABLE departments CASACDE;

5. DDL Logging:

DDL logging means logging of oracle data definition language (DDL) commands and not the redo logging of DML commands (insert/update/delete).

In Oracle 12c there is a new initialization parameter ENABLE_DDL_LOGGING which can be set to TRUE or FALSE and this can also be set by use of the  ALTER SYSTEM and ALTER SESSION commands for database-wide logging or logging of just the current session, respectively.

When set to TRUE this flag has the effect of logging all DDL commands executed in the database to an XML file located in c:\app\oracle\diag\rdbms\orcl\orcl\log\ddl

6. Identity Columns:

Another Oracle 12c new feature in SQL is the ANSI standard IDENTITY column which is effectively a short cut to declaring and using a sequence.

There are three basic options for using Identity columns – the next sequence value can be used by default, by default on null, or always.

Let’s look at some examples.

SQL> CREATE TABLE test (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY);

Table created

SQL> INSERT INTO test VALUES (DEFAULT);

1 row created.

SQL> INSERT INTO test VALUES (9999);

1 row created.

SQL> SELECT * FROM test;

col1

1
9999

Note that we have to use the keyword DEFAULT otherwise the following error is generated “ORA-01400: cannot insert NULL into (“HR”.”TEST”.”COL1″)”.

The next example demonstrates the use of DEFAULT ON NULL.

SQL> CREATE TABLE test2 (col1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

Table created

SQL> INSERT INTO test2 VALUES (DEFAULT);

1 row created.

SQL> INSERT INTO test2 VALUES (NULL);

1 row created.

SQL> INSERT INTO test2 VALUES (9999);

1 row created.

SQL> SELECT * FROM test2;

col1

1
2
9999

The difference is that we can use the keyword NULL and the column will be populated for us from the sequence.

The next example demonstrates the use of GENERATED ALWAYS AS IDENTITY.

SQL> CREATE TABLE test3 (col1 NUMBER GENERATED ALWAYS AS IDENTITY);

Table created

SQL> INSERT INTO test3 VALUES (999);

INSERT INTO test3 VALUES (999);
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

SQL> INSERT INTO test3 VALUES (DEFAULT);

1 row created.

SQL> INSERT INTO test3 VALUES (NULL);

1 row created.

SQL> SELECT * FROM test3;

COL1

1
2

SQL>

As the previous example shows, we cannot provide a value for a column that is defined as GENERATED ALWAYS.

As with sequences, identity columns can be defined with a starting value, an increment and how many values to cache.

SQL> CREATE TABLE test3 (col1 NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 101 INCREMENT BY 5));

7. Multiple Indexes:
One Oracle 12c new feature is to allow the same column or columns to have more than one index defined although only one index can be visible at any one time.

You might be wondering what is the point of that. Well, there are a couple of uses: you might want to test what the effect on your application(s) would be of dropping a particular index; or changing the type of index from B-tree to butmap or vice versa; or you might have different applications that would benefit from different types of index. For example oltp during the day using b-tree indexes and over-night reporting or batch processing using bitmap indexes.

Being able to define multiple indexes on the same column(s) enables you to do this without having to drop an existing index or mark it as unusable and later rebuild it.

© 2015, www.techkatak.com. All rights reserved.

3 thoughts on “Oracle 12c new features (Part – 2)

Comments are closed.