UNION or UNION ALL consists of many queries(branches) that in pre 12C releases were executed one by one.

<BRANCH1>
UNION
<BRANCH2>

<BRANCH1>
UNION ALL
<BRANCH2>

So in pre 12C first  is processed BRANCH1 then BRANCH2. Of course each individual query(branch) can be processed in serial or in parallel but only one branch at time.

Oracle 12C allows to run branches(statement) of UNION or UNION ALL concurrently. It means that BRANCH2 can be processed together with BRANCH1 :). It can even return data faster than BRANCH1.

This feature is turned on automatically and entire UNION or UNION ALL is processed in parallel if:

• OPTIMIZER_FEATURE_ENABLED set to 12.1 or higher
• one of a branch is considered being processed in parallel

Continue reading →

This new feature in 12C allows to make invisible columns for generic queries, operations. To make invisible column you need to use INVISIBLE clause.

Following operations don’t see invisible columns

• SELECT * FROM statements in SQL
• DESCRIBE commands in SQL*Plus
• %ROWTYPE attribute declarations in PL/SQL
• Describes in Oracle Call Interface (OCI)

Continue reading →

New extensions are added for SQL in 12C

• CROSS APPLY is another variation to make simple join ANSI CROSS JOIN
• OUTER APPLY is another variation to make left outer join ANSI LEFT OUTER APPLY

• LATERAL is another variation to make simple join

Continue reading →

This new Oracle 12C feature enables to define auto numbering for a column. Only one column can be defined as identity.

It is recommended to read about other extensions in 12C for default column values

• Default Values for Columns Based on Oracle Sequences
• Default Values for Columns on Explicit NULL Insertion

identity_options – defines sequence generator. It creates a sequence in database that is later used to generate values for identity column.

Continue reading →

DEFAULT ON NULL in Oracle 12c  assign default value if INSERT attempts to assign a value that evaluates to NULL.

• DEFAULT ON NULL <VALUE>

drop table test_tbl;

create table test_tbl
( id  number default on null 5,
  id1 number);

insert into test_tbl values(null, 10);
insert into test_tbl values(100, 20);

select * from test_tbl;

        ID        ID1
---------- ----------
         5         10 
       100         20

Continue reading →

In Oracle 12c DEFAULT values for columns can directly refer to Oracle sequences.

• DEFAULT sequence.NEXTVAL
• DEFAULT sequence.CURRVAL

create sequence test_seq;
create synonym test_seq1 for test_seq;

create table test_tbl
(id  number default test_seq.currval,
 id1 number default test_seq.nextval,
 id2 number default test_seq1.currval,
 id3 number);

insert into test_tbl(id3) values(10);
insert into test_tbl(id3) values(20);
insert into test_tbl(id3) values(30);

select * from test_tbl;
        ID        ID1        ID2        ID3
---------- ---------- ---------- ----------
         1          1          1         10 
         2          2          2         20 
         3          3          3         30

Continue reading →

Introduction

New higher limits (in bytes) are available for following types

• VARCHAR2 – 32767 bytes
• NVARCHAR2 – 32767 bytes
• RAW – 32767 bytes

Previous limits prior to Oracle Database 12c

• VARCHAR2 – 4000 bytes
• NVARCHAR2 – 4000 bytes
• RAW – 2000 bytes

NOTE – remember new limits are always 32767 bytes even if you are using CHAR semantic

Continue reading →

Smart Flash Cache concept is not new in Oracle 12C – DB Smart Flash Cache in Oracle 11g.

In this release Oracle has made changes related to both initialization parameters used by DB Smart Flash cache. Now you can define many files|devices and its sizes for “Database Smart Flash Cache” area. In previous releases only one file|device could be defined.

DB_FLASH_CACHE_FILE = /dev/sda, /dev/sdb, /dev/sdc

DB_FLASH_CACHE_SIZE = 32G, 32G, 64G

So above settings defines 3 devices which will be in use by “DB Smart Flash Cache”

• /dev/sda – size 32G
• /dev/sdb – size 32G
• /dev/sdc – size 64G

New view V$FLASHFILESTAT  – it’s used to determine the cumulative latency and read counts of each file|device and compute the average latency.

Have a fun 🙂

Tomasz

New keywords OFFSET and FETCH provides ANSI SQL standard to limit the number of rows returned and to specify a starting row for the return set.

Note

• ROW and ROWS has got the same meaning and can be used interchangeably
• FIRST and NEXT has got the same meaning and can be used interchangeably

[ OFFSET offset ROWS]
FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

Continue reading →

It’s new option to return many ref cursors in PL/SQL implicit.

When code is migrated to Oracle Database from other vendors’ environments (Microsoft etc.), the capability will remove the need to rewrite code that takes advantage of implicit result set communication.

This feature is delivered by DBMS_SQL.RETURN_RESULT procedure which is responsible for creation of implicit result sets. Following procedure hasn’t got any explicit declaration of out variables. I love it :).

create or replace procedure return_many_cursors
authid current_user
is
  v_c1 sys_refcursor;
  v_c2 sys_refcursor;
  v_c3 sys_refcursor;
begin
  open v_c1 for 'select object_name from user_objects';

  dbms_sql.return_result(v_c1);

  open v_c2 for 'select instance_name from v$instance';

  dbms_sql.return_result(v_c2);

  open v_c3 for 'select name from v$database';

  dbms_sql.return_result(v_c3);
end;
/

Continue reading →