(Part 1) New features in Oracle Database 12c : SQL Enhancements

(Part 1) New features in Oracle Database 12c : SQL Enhancements

 

Native SQL Support for Query Row Limits and Row Offsets

Oracle 12c has introduced the row limiting clause to simplify Top-N queries and paging through ordered result sets. The FETCH FIRST and OFFSET clauses provides native SQL language support to limit the number of rows returned and to specify a starting row for the return set.

[ OFFSET offset { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ]

Row Limiting: Examples 

SQL> SELECT employee_id, last_name FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY;

SQL> SELECT employee_id, last_name FROM employees ORDER BY employee_id OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

SQL> SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS ONLY;

SQL> SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS WITH TIES;

 

Increased Size Limit for VARCHAR2, NVARCHAR2, and RAW Data Types

The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increased from 4,000 to 32,767 bytes.

Beginning with Oracle Database 12c, you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your database supports this new maximum size by setting the initialization parameter MAX_STRING_SIZE as follows:

·         If MAX_STRING_SIZE = STANDARD, then the size limits for releases prior to Oracle Database 12c apply: 4000 bytes for the VARCHAR2 and NVARCHAR2 data types, and 2000 bytes for the RAW data type. This is the default.

·         If MAX_STRING_SIZE = EXTENDED, then the size limit is 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types.

A VARCHAR2 or NVARCHAR2 data type with a declared size of greater than 4000 bytes, or a RAW data type with a declared size of greater than 2000 bytes, is an extended data type. Extended data type columns are stored out-of-line, leveraging Oracle's LOB technology. The LOB storage is always aligned with the table. In tablespaces managed with Automatic Segment Space Management (ASSM), extended data type columns are stored as SecureFiles LOBs. Otherwise, they are stored as BasicFiles LOBs. The use of LOBs as a storage mechanism is internal only.

Oracle strongly discourages the use of BasicFiles LOBs as a storage mechanism. BasicFiles LOBs not only impose restrictions on the capabilities of extended data type columns, but the BasicFiles data type is planned to be deprecated in a future release.

Note:

You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. However, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a Non-CDB

1. Shut down the database.
2. Restart the database in UPGRADE mode.
3. Change the setting of MAX_STRING_SIZE to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
5. Restart the database in NORMAL mode.

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a PDB

1. Shut down the PDB.
2. Reopen the PDB in migrate mode. ALTER PLUGGABLE DATABASE pdb-name OPEN UPGRADE;
3. Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script in the PDB. You must be connected AS SYSDBA to run the script.
5. Reopen the PDB in NORMAL mode.

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in an Oracle RAC database:

1. Shut down all of the Oracle RAC database instances, except one.
2. Restart the Oracle RAC database instance in UPGRADE mode.
3. Change the setting of MAX_STRING_SIZE to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script in the Oracle RAC database instance. You must be connected AS SYSDBA to run the script.
5. Restart the Oracle RAC database instance in NORMAL mode.
6. Restart the other Oracle RAC database instances in NORMAL mode.

DEFAULT Values for Table Columns

·         Default Values for Columns Based on Oracle Sequences

Default values for columns can directly refer to Oracle sequences. Valid entries are sequence.CURVAL and sequence.NEXTVAL. Providing the functionality to directly refer to a sequence as a default value expression simplifies code development.

 

SQL> CREATE SEQUENCE test_seq;

SQL> CREATE TABLE test_tab (id NUMBER DEFAULT test_seq.NEXTVAL, description VARCHAR2(30));

SQL> INSERT INTO test_tab (description) VALUES ('DESCRIPTION only');

SQL> INSERT INTO test_tab (id, description) VALUES (122, 'ID=122 and DESCRIPTION');

SQL> INSERT INTO test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

SQL> SELECT * FROM test_tab;

Notes:

During table creation, the sequence must exist and you must have select privilege on.

The users performing inserts against the table must have select privilege on the sequence, as well as insert privilege on the table.

If the sequence is dropped after table creation, subsequent inserts will error.

·         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 SEQUENCE test_seq2;

SQL> CREATE TABLE test_tab2 (id NUMBER DEFAULT ON NULL test_seq2.NEXTVAL, description VARCHAR2(30));

SQL> INSERT INTO test_tab2 (description) VALUES ('DESCRIPTION only');

SQL> INSERT INTO test_tab2 (id, description) VALUES (NULL, 'NULL,DESCRIPTION');

SQL> SELECT * FROM test_tab2;

·         Metadata-Only DEFAULT Column Values for NULL Columns

The default values of columns are maintained in the data dictionary for columns specified as NULL.

Adding new columns with DEFAULT values no longer requires the default value to be stored in all existing records. This not only enables a schema modification in sub-seconds and independent of the existing data volume, it also does not consume any space.

Oracle 11g introduced the concept of metadata-only default values. Adding a NOT NULL column with a DEFAULT clause to an existing table involved just a metadata change, rather than a change to all the rows in the table. Queries of the new column were rewritten by the optimizer to make sure the result was consistent with the default definition.

Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. As a result, adding a new column with a DEFAULT clause to an existing table will be handled as a metadata-only change, regardless of whether that column is defined as NOT NULL or not. This represents both a space saving and performance improvement.

IDENTITY Columns

Table columns have been enhanced to support the American National Standards Institute (ANSI) SQL keyword IDENTITY.

The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]

ALWAYS If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned. This is the default.

BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column. If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

identity_options: use the identity_options clause to configure the sequence generator. The identity_options clause has the same parameters as the CREATE SEQUENCE statement.

SQL> CREATE TABLE test_tab1 (Id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30));

SQL> CREATE TABLE test_tab2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY, description VARCHAR2(30));

SQL> CREATE TABLE test_tab3 (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, description VARCHAR2(30));

The [DBA|ALL|USER]_TAB_IDENTITY_COLS views show information about identity columns

 Invisible Columns

Invisible columns are not seen unless specified explicitly in the SELECT list. Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns. You can make a column invisible during table creation or when you add a column to a table, and you can later alter the table to make the same column visible. You can also alter a table to make a visible column invisible.

The idea of invisible columns is similar to the idea of creating a view on a table while leaving out the columns that you do not want the end-user to see.

·         The SELECT * syntax will not display an INVISIBLE column. However, if you include an INVISIBLE column in the select list of a SELECT statement, then the column will be displayed.

SQL> CREATE TABLE test_tab1 ( a number, b number invisible, c number);

SQL> desc test_tab1

 Name                                    Null?    Type

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

 A                                        NUMBER

 C                                        NUMBER

SQL> SELECT * FROM test_tab1;

    A         C

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

    1         3

SQL> SELECT a,b,c FROM test_tab1;

    A         B         C

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

    1         2         3

·         You cannot implicitly specify a value for an INVISIBLE column in the VALUES clause of an INSERT statement. You must specify the INVISIBLE column in the column list.

SQL> INSERT INTO test_tab1 VALUES (1,2,3);

INSERT INTO test_tab1 VALUES (1,2,3)

            *

ERROR at line 1:

ORA-00913: too many values

SQL>  INSERT INTO test_tab1(a,b,c) VALUES (1,2,3);

1 row created.

SQL> INSERT INTO test_tab1 VALUES (1,2);

1 row created.

·         You can configure SQL*Plus to allow INVISIBLE column information to be viewed with the DESCRIBE command.

SQL> SET COLINVISIBLE ON

SQL> desc test_tab1

 Name                                    Null?    Type

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

 A                                        NUMBER

 C                                        NUMBER

 B (INVISIBLE)                            NUMBER

·         Restrictions on VISIBLE and INVISIBLE Columns 

    INVISIBLE columns are not supported in external tables, cluster tables, or temporary tables.

    You cannot make a system-generated hidden column visible.

The ALL_, DBA_, and USER_TAB_COLS views show information about Hidden columns,  query the HIDDEN_COLUMN and USER_GENERATED columns of.

SQL> Select TABLE_NAME,COLUMN_NAME,COLUMN_ID,HIDDEN_COLUMN,USER_GENERATED from USER_TAB_COLS where TABLE_NAME='TEST_TAB1';

TABLE_NAME COLUMN_NAME   COLUMN_ID HID USE

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

TEST_TAB1 C                    2 NO  YES

TEST_TAB1 B                       YES YES

TEST_TAB1 A                    1 NO  YES

Define PL/SQL Subprograms in a SQL Statement

With Oracle Database 12c, you can now define PL/SQL functions and procedures in the WITH clause of a subquery and then use them as you would any other built-in or user-defined function. Example of In Line PL/SQL Function in SQL query:

WITH

 FUNCTION betwnstr (

     string_in   IN VARCHAR2, start_in IN PLS_INTEGER, end_in IN PLS_INTEGER)

 RETURN VARCHAR2

 IS

 BEGIN

   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));

 END;

SELECT betwnstr (last_name,1,5) FROM employees

So why would a developer want to copy logic from a PL/SQL function into a SQL statement? To improve performance. When call own PL/SQL function in a SQL statement, the SQL engine must perform a performance-affecting context switch to the PL/SQL engine. Moving the code inside the SQL statement means that that context switch no longer occurs.

Enhanced Online DDL Capabilities

Several schema maintenance DDL operations no longer require blocking locks, making these operations non-intrusive and transparent for online use. The improved schema maintenance DDL operations are:

·         DROP INDEX ONLINE

·         DROP CONSTRAINT ONLINE

·         SET UNUSED COLUMN ONLINE

·         ALTER INDEX UNUSABLE ONLINE

·         ALTER INDEX [VISIBLE | INVISIBLE]

Removing internal blocking locks enables simpler and more robust application development, especially for application migrations. It avoids application disruptions for many of the typical schema maintenance operations.

NO REDO for UNDO of Global Temporary Tables

Global Temporary Tables hold temporary non persistent data. The data in Global Temporary Tables are either deleted after commit or kept until the session is connected depending of the definition of the Global Temporary Tables.(ON COMMIT PRESERVE OR DELETE ROWS ).

Global Temporary Tables They can be part of a transaction and also support rollback and commit. For this to happen, Oracle DB has to maintain some undo information for each transaction. And until now, any block change in UNDO also generates some REDO. Since the data is not persistent by definition of Global Temporary Tables, it looks like a waste of precious cycles generating REDO.

In 12c, oracle can now write UNDO of Global Temporary Tables in temp tablespace instead of UNDO tablespace. This way you can now avoid generation of REDO for the UNDO, while still having the ability to rollback a transaction.With this feature, some of the I/O contention is reduced for online redo logfiles.

TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log. The default choice for database transactions has been to have a single undo log per transaction. This parameter, at the session level / system level scope, lets a transaction split its undo log into temporary undo log (for changes on temporary objects) and permanent undo log (for changes on persistent objects). When TEMP_UNDO_ENABLED is set to true 0, this feature is enabled. The temporary undo feature is enabled

Once the value of the parameter is set, it cannot be changed for the lifetime of the session. If the session has temporary objects using temporary undo, the parameter cannot be disabled for the session. Similarly, if the session already has temporary objects using regular undo, setting this parameter will have no effect.

SQL> alter system set temp_undo_enabled=TRUE;

SQL> CREATE GLOBAL TEMPORARY TABLE temp_table (col1 NUMBER,col2 NUMBER) ON COMMIT DELETE ROWS;

Table created.

SQL> set autotrace traceonly statistics

SQL> Insert into temp_table(col1,col2) select object_id,object_id from dba_objects where rownum < 90001;

90000 rows created.

Statistics

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

        28  recursive calls

       1275  db block gets

       3136  consistent gets

         0  physical reads

       272  redo size

       854  bytes sent via SQL*Net to client

       899  bytes received via SQL*Net from client

         3  SQL*Net roundtrips to/from client

         1  sorts (memory)

         0  sorts (disk)

      90000  rows processed

SQL> update temp_table set col1=col2+1;

90000 rows updated.

Statistics

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

        11  recursive calls

      95966  db block gets

       473  consistent gets

         0  physical reads

         0  redo size

       856  bytes sent via SQL*Net to client

       834  bytes received via SQL*Net from client

         3  SQL*Net roundtrips to/from client

         1  sorts (memory)

         0  sorts (disk)

      90000  rows processed

Truncate Table Cascade

In Oracle 12c new word CASCADE can be specified for TRUNCATE command. If you specify CASCADE, then Oracle Database truncates all child tables that reference TABLE with an enabled ON DELETE referential constraint. This is a recursive operation that will truncate all child tables, grandchild tables, and so on, using the specified options.

SQL> create table test_tab_ma(col1 number primary key, col2 number);

Table created.

SQL> create table test_tab_ch(col1 number primary key, col2 number);

Table created.

SQL> alter table test_tab_ch add constraint child_fk foreign key (col2) references test_tab_ma ON DELETE CASCADE;

Table altered.

SQL> insert into test_tab_ma values(1,1);

1 row created.

SQL> insert into test_tab_ch values(1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table test_tab_ma CASCADE;

Table truncated.

SQL> select count(*) from test_tab_ma;

  COUNT(*)

----------

        0

SQL> select count(*) from test_tab_ch;

  COUNT(*)

----------

        0