Oracle
Database 12c introduces a new multitenant architecture that makes it easy to
deploy and manage database clouds.
DEFAULT
Values Using Sequences
In Oracle 12c, it is now possible to specify the CURRVAL and NEXTVAL sequence
pseudocolumns as the default values for a column. You should also consider
using Identity columns for this purpose.
In the following example you can see the effect of
specifying a sequence as the default value for a column. The default value is
only used when the column is not referenced by the insert. This behaviour can
be modified using the ON NULL clause described in the next section.
CREATE SEQUENCE t1_seq;
CREATE TABLE t1 (
id NUMBER DEFAULT
t1_seq.NEXTVAL,
description VARCHAR2(30)
);
INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
INSERT INTO t1 (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO t1 (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
SELECT * FROM t1;
ID DESCRIPTION
---------- ------------------------------
1 DESCRIPTION only
999 ID=999 and DESCRIPTION
ID=NULL and
DESCRIPTION
3 rows selected.
SQL>The fact we can use both the NEXTVAL and CURRVAL
pseudocolumns gives us the ability to auto-populate master-detail
relationships, as shown below.
CREATE SEQUENCE master_seq;
CREATE SEQUENCE detail_seq;
CREATE TABLE master (
id NUMBER DEFAULT
master_seq.NEXTVAL,
description VARCHAR2(30)
);
CREATE TABLE detail (
id NUMBER DEFAULT
detail_seq.NEXTVAL,
master_id NUMBER DEFAULT master_seq.CURRVAL,
description VARCHAR2(30)
);
Oracle 12c Rel 1 New
Feature-Implicit Result Set :
Now we can create a stored procedure to return one or more resultsets
containing this data. Also you need not explicitly define the ref cursor as OUT
parameter.
create or replace procedure pimprefcur
authid current_user
is
vemp sys_refcursor;
vdept sys_refcursor;
begin...
open vemp for 'select ename from emp where empno=7788';
dbms_sql.return_result(vemp);
open vdept for 'select dname from dept where deptno=10';
dbms_sql.return_result(vdept);
end;
/
---executing the above procedure would display the values
returned by both the
---ref cursors
SQL> exec pimprefcur;
PL/SQL procedure successfully completed.
ResultSet #1
ENAME
----------------------
SCOTT
ResultSet #2
DNAME
----------------
ACCOUNTING
Oracle
12c Rel1 New Feature: Online Migration of Table Partition & Sub partition
made online unblocking DDL and DML:
DML and DDL changes are made to the partition. It removes the read only state
for the actual MOVE command.
Global Index is being maintained so does not require a manual rebuild
--Following statem...ent is used to move partition or
subpartition when tablespace is offline
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO
tablespace tablespace_name;
--Following statement is used to move parition or
subpartition online maintaining any local and global index on the table.
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO
tablespace tablespace_name UPDATE INDEXES ONLINE;
Oracle 12c Rel1 New Feature:
Temporary Undo:
Prior to Oracle 12c R1, any DML you do on a "Global temporary table"
(GTT) would generate undo and this would inturn generate redo records.
From Oracle 12c R1 the UNDO generated by temporary tables
can now be stored in a temporary tablespace instead of storing it in UNDO
tablespace.
The advantage of temporary undo : 1) reduction in undo tablespace
2) less redo data generation
To use this feature (at system or session), enable
TEMP_UNDO_ENABLED initialization parameter
SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=TRUE;
*If you are doing lot of temporary work where you use GTT
think of using Temporary Undo
Oracle
12c Rel1 New Feature: Invisible Columns in Table:
You will be able to hide columns in a table.
SQL> CREATE TABLE emp (empno number(6), ename
varchar2(50), sal number(10,2) INVISIBLE);
--You can also alter a table to "VISIBLE" or
"INVISIBLE":
SQL> ALTER TABLE emp MODIFY (sal visible);
--From SQL prompt when you issue DESC it will not display
the invisible column. To display invisible col...umn from within SQL Plus,
issue:
SQL> SET COLINVISIBLE ON
--When you insert values into the table having invisible
column, have to include all the columns names :
SQL> Insert into emp (empno, ename, sal) values (1001,'Sairam',7868);
1 row created.
--If you try to insert without specifying column names and
specily all the column valus it will throw an error
SQL> Insert into emp values (1001,'Sairam',7868);
*
ERROR at line 1:
ORA-00913: too many values
---When inserting just specifying only the visible column
values will work fine
SQL> Insert into emp values (1001,'Sairam');
1 row created.
--remember generic queries like the following will not
show the result of invisible column
SQL> select * from emp;
Multiple
indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or
set of columns in any form. For example, if you have an index on column {a} or
columns {a,b}, you can’t create another index on the same column or set of
columns in the same order. In 12c, you can have multiple indexes on the same
column or set of columns as long as the index type is different. However, only
one type of index is usable/visible at a given time. In order to test the
invisible indexes, you need to set the
optimizer_use_use_invisible_indexes=true.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);
SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
Adding
multiple new partitions
Before Oracle 12c R1, it was only possible to add one new partition at a time
to an existing partitioned table. To add more than one new partition, you had
to execute an individual ALTER TABLE ADD PARTITION statement to every new
partition. Oracle 12c provides the flexibility to add multiple new partitions
using a single ALTER TABLE ADD PARTITION command. The following example
explains how to add multiple new partitions to an existing partitioned table:
SQL> CREATE TABLE emp_part
(eno number(8), ename varchar2(40), sal number (6))
PARTITION BY RANGE (sal)
(PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p3 VALUES LESS THAN (30000)
);
Now lets add a couple of new partitions:
SQL> ALTER TABLE emp_part ADD PARTITION
PARTITION p4 VALUES LESS THAN (35000),
PARTITION p5 VALUES LESS THAN (40000);
The following example explains how to drop or truncate
multiple partitions on an existing partitioned table:
SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;
To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES
clause, shown below:
SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE
GLOBAL INDEXES;
SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
ROW
limiting for Top-N result queries
There are various indirect approaches/methods exist to fetch Top-N query
results for top/bottom rows in the previous releases. In 12c, retrieving Top-N
query results for top/bottom rows simplified and become straight forward with
the new FETCH FIRST|NEXT|PERCENT clauses.
In order to retrieve top 10 salaries from EMP table, use
the following new SQL statement:
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY;
The following example fetches all similar records of Nth row. For example, if
the 10th row has salary of 5000 value, and there are other employees whose
salary matches with the Nth value, the will also be fetched upon mentioning
WITH TIES clause.
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY WITH TIES;
The following example limits the fetch to 10 per cent from
the top salaries in the EMP table:
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
The following example offsets the first 5 rows and will display the next 5 rows
from the table:
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
All these limits can be very well used within the PL/SQL block too.
BEGIN
SELECT sal BULK COLLECT INTO sal_v FROM EMP
FETCH FIRST 100 ROWS ONLY;
END;
WITH
clause improvements
In 12c, you can have faster running PL/SQL function/procedure in SQL, that are
defined and declared within the WITH clause of SQL statements. The following
examples demonstrate how to define and declare a procedure or function within
the WITH clause:
WITH
PROCEDURE|FUNCTION test1 (…)
BEGIN
<logic>
END;
SELECT <referece_your_function|procedure_here> FROM table_name;
/
Although you can’t use the WITH clause directly in the PL/SQL unit, it can be
referred through a dynamic SQL within that PL/SQL unit.
Extended
data types
In 12c, the data type VARCHAR2, NAVARCHAR2, and RAW size will support up to
32,767 bytes in contrast to 4,000 and 2,000 in the earlier releases. The
extended character size will reduce the use of going for LOB data types,
whenever possible. In order to enable the extended character size, you will
have to set the MAX_STRING_SIZE initialization database parameter to EXTENDED.
