This content has been copied from several websites so that you can easily view them from one page. Credit goes to people who put their time and effort to create this content.
Basic Oracle enhancements (not too technical)
Basic Oracle enhancements (not too technical)
· PL/Sql code in sql statements
The
In-Line PL/SQL Function (or procedure) can even be a recursive one:
· Can have invisible columns
In Oracle 11g R1, Oracle
introduced a couple of good enhancements in the form of invisible indexes and
virtual columns. Taking the legacy forward, invisible column concepts has been
introduced in Oracle 12c R1. I still remember, in the previous releases, to
hide important data –columns from being displayed in the generic queries– we
used to create a view hiding the required information or apply some sort of
security conditions.
In 12c R1, you can now
have an invisible column in a table. When a column is defined as invisible, the
column won’t appear in generic queries, unless the column is explicitly
referred to in the SQL statement or condition, or DESCRIBED in the table
definition. It is pretty easy to add or modify a column to be invisible and
vice versa:
SQL> CREATE TABLE emp (eno number(6), ename name
varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly
refer to the invisible column name with the INSERT statement to insert the
database into invisible columns. A virtual column or partition column can be
defined as invisible too. However, temporary tables, external tables and
cluster tables won’t support invisible columns.
· 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.
·
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 example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);
SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME)
INVISIBLE;
· 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;
· Pluggable databases
o Online rename and relocation of an active data file
Unlike
in the previous releases, a data file migration or renaming in Oracle database
12c R1 no longer requires a number of steps i.e. putting the tablespace in READ
ONLY mode, followed by data file offline action. In 12c R1, a data file can be
renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL
statement. While the data file is being transferred, the end user can perform
queries, DML and DDL tasks. Additionally, data files can be migrated between
storages e.g. from non-ASM to ASM and vice versa.
Rename
a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate
a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA';
Migrate
a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite
the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy
the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
You can monitor the progress while a data file being moved by
querying the v$session_longops dynamic view. Additionally, you can also
refer the alert.log of the database where Oracle writes the details about
action being taken place.
· Enhanced Default value handling
o 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.
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.
Creating a Table with a
DEFAULT ON NULL Column Value: Example
The following statement creates a table
myemp, which can be used to store employee data. The department_id column is defined with aDEFAULT ON NULL column value of 50.
Therefore, if a subsequent INSERT statement attempts to assign a NULL value to department_id, then the value of 50 will be assigned instead.CREATE TABLE myemp (employee_id number, last_name varchar2(25),
department_id NUMBER DEFAULT ON NULL 50 NOT NULL);
In the
employees table, employee_id 178 has a NULL value for department_id:SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IS NULL;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------------------- -------------
178 Grant
Populate the
myemp table with the employee_id, last_name, and department_id column data from the employees table:INSERT INTO myemp (employee_id, last_name, department_id)
(SELECT employee_id, last_name, department_id from employees);
In the
myemp table, employee_id 178 has a value of 50 for department_id:SELECT employee_id, last_name, department_id
FROM myemp
WHERE employee_id = 178;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------------------- -------------
178 Grant 50
o IDENTITY Columns
Table columns have been enhanced to support the American
National Standards Institute (ANSI) SQL keyword
IDENTITY.
This provides standards based approach to the declaration of
automatically incrementing columns simplifying application development and
making the migration of DDL to Oracle simpler.
Creating a Table with
an Identity Column: Examples
The following statement creates a table
t1 with an identity column id. The sequence generator will always assign increasing integer
values to id, starting with 1.CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY);
The following statement creates a table
t2 with an identity column id. The sequence generator will, by default, assign increasing
integer values to id in increments of 10 starting with 100.CREATE TABLE t2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10));
·
Adaptive Query
Optimization
Adaptive Query
Optimization is a set of capabilities that enable the optimizer to make
run-time an adjustment to execution plans and discover additional information
that can lead to better statistics.
This new approach is
extremely helpful when existing statistics are not sufficient to generate an
optimal plan. There are two distinct aspects in Adaptive Query Optimization,
adaptive plans, which focuses on improving the initial execution of a query and
adaptive statistics, which provide additional information to improve subsequent
executions.
