Exploring Oracle Database 12c 12c New Features and Best Practices for Database Developers and DBAs Ami Aharonovich Oracle ACE & OCP [email protected]

About Me
Oracle ACE
Oracle Certified Professional DBA (OCP)
CEO, Brillix-DBAces
President, Israel Oracle User Group
Oracle DBA consultant and instructor, dealing with Oracle database core technologies
Frequent speaker at Oracle Open World annual event and various user group conferences around the globe

About BrillixBrillix-DBAces We are committed to provide the highest quality of services delivered by our dedicated team of industry’s top experts. We offer:
Complete end-to-end 24x7 solutions based on best-ofbreed innovations in database, security and big data technologies
Comprehensive security solutions and services for leading database platforms and business applications
Professional trainings led by our team of Oracle ACEs and Oracle Certified Professionals

Agenda
SQL Developer


Multiple Indexes


SQLcl


Data Redaction


Invisible Columns


Better Column Addition


Improved Defaults


Statistics Enhancements


Identity Column


Adaptive Execution Plans


PL/SQL from SQL


Partitioning New Features


Row Limit Clause


Multitenant Architecture

SQL Developer 4.1.2
Free development environment that simplifies development and management of Oracle database: ▫ ▫ ▫ ▫ ▫ ▫

Complete end-to-end development of PL/SQL applications Worksheet for running queries and scripts DBA console for managing the database reports interface complete data modeling solution migration platform

SQL Developer 4.1.2
Clone PDB to Oracle Database Cloud Service
Import Data Wizard
Instance Viewer
Multi-Cursor Editing
Released October 23, 2015

SQLcl (EA)
The “New SQL*Plus” (SQL*Plus on steroids)
No installation required
TAB completion
Multi-line console editing
History
Alias
SQLFORMAT
INFORMATION

Invisible Columns
Make changes to a table without disrupting applications that use the table
Any access of a table does not show the invisible column
To use (query/change) the invisible column, explicitly specify the invisible column in the SQL statement
Make a column invisible during table creation or when you add a column to a table
In SQL*Plus, use SET COLINVISIBLE ON to display the invisible column when using the DESCRIBE command Invisible_Column.sql

Improved Defaults
Default value using a sequence
Default value when null is inserted
Identity column type

Default Value Using a Sequence
You can specify CURRVAL and NEXTVAL as default values for a column
Default value is used when the column is not referenced by the insert or when the DEFAULT keyword is used

Default_with_Sequence.sql

Default Value on Explicit Nulls
In an insert statement, when the column is explicitly referenced, even when using the value NULL, the default value is not used
Oracle database 12c allows you to modify this behavior using the ON NULL clause in the default definition

Default_with_Null.sql

Identity Column Type
In previous releases, there was no direct equivalent of the AutoNumber or Identity functionality of other database engines
This behavior had to be implemented using a combination of sequences and trigger
Oracle 12c introduces the ability to define an identity clause against a table column defined using a numeric type
User should have the create sequence privilege
Identity columns are always not null

Identity Column Type – Options
ALWAYS ▫ Forces the use of the identity. If an insert statement references the identity column, an error is produced


BY DEFAULT ▫ Allows using the identity if the column isn't referenced in the insert statement. If the column is referenced, the specified value will be used in place of the identity


BY DEFAULT ON NULL ▫ Allows the identity to be used if the identity column is referenced, but a value of NULL is specified

Identity.sql

Identity Column Type – Restrictions
You can specify only one identity column per table
When specifying identity clause, you must specify a numeric data type for datatype in the column definition clause
When specifying identity clause, you cannot specify the DEFAULT clause in the column definition clause
When specifying identity clause, the NOT NULL constraint is implicitly specified
CREATE TABLE AS SELECT will not inherit the identity property on a column

Calling PL/SQL from SQL
You can 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
The “;” does not work as a terminator to the SQL statement when the PL/SQL declaration is included in the WITH clause
Functions defined in the PL/SQL declaration section of the WITH clause take precedence over objects with the same name defined at the schema level
Provides better performance as compared with schema level functions WITH_PLSQL.sql

Row Limit Clause
Oracle database 12c introduces support for the ANSIstandard FETCH FIRST/NEXT and OFFSET clauses
Clause enables to easily retrieve the first N rows from a result set, or alternatively the first N rows after skipping over a set of rows
Clause is simply added to the end of any SQL SELECT statement

Row_Limit_Clause.sql

Multiple Same Column Indexes
Ability to create more than one index on the same set of attributes
Why? What for? ▫ Testing ▫ Availability


You can create multiple indexes, as long as only one index is “visible”

Multiple_Indexes.sql

Data Redaction – Overview
Dynamic redaction based on username, IP address, application context and other factors
Transparent, consistent enforcement in the database
No measurable impact on production workloads

Data Redaction – Examples

Better Column Addition
When adding a column with default value, while the column can be null: ▫ The statement will not issue an update to all records of the table ▫ When a user selects the column, Oracle gets the default value from the data dictionary and returns it to the user ▫ No undo/redo generation


(in Oracle 11g the column had to be NOT NULL…)

Statistics Enhancements
Statistics during loads: ▫ Table statistics are gathered automatically during bulk loads (CTAS and INSERT INTO … SELECT) ▫ Improved performance: avoids an additional table scan to gather table statistics ▫ Improved manageability: no user intervention is required to gather statistics after a bulk load ▫ To disable use hint: NO_GATHER_OPTIMIZER_STATISTICS

Statistics Enhancements
Session private statistics for GTT’s: ▫ ▫ ▫ ▫

Different set of statistics for each session Queries against GTT use statistics from their own session Improves the performance and manageability of GTT’s Reduces the possibilities of errors in the cardinality estimates for GTT’s and ensures that the optimizer has the data to identify optimal execution plans

Adaptive Execution Plans
Enables the Optimizer to make runtime adjustments to execution plans and discover additional information that can lead to better statistics
Good SQL execution without intervention
Final plan is based on rows seen during execution
Eliminates bad effects of skew

Oracle 12c 12c Partitioning New Features
Partial Indexes for Partitioned Tables
ONLINE Move Partitions
Partition Maintenance Operations on Multiple Partitions
Asynchronous Global Index Maintenance for DROP and TRUNCATE Partition
Interval Reference Partitioning

Oracle white paper: Partitioning with Oracle Database 12c http://www.oracle.com/technetwork/database/options/partitioning/partitioning-wp-12c-1896137.pdf

Oracle 12c 12c Partitioning New Features
Partial Indexes for Partitioned Tables ▫ New index attribute only applicable to indexes on partitioned tables ▫ Indexes can be created on a subset of the partitions of a table ▫ Provide more flexibility in index creation for partitioned tables ▫ For example, you can choose not to index the most recent partition to avoid any index maintenance work at data insertion time, therefore maximizing data load speed

Partial_Indexes.sql

Oracle 12c 12c Partitioning New Features
ONLINE Move Partitions ▫ Partition maintenance operations can be done in a completely online fashion, allowing DML operations to occur while the data maintenance operation is in process ▫ ALTER TABLE ... MOVE PARTITION becomes non-blocking online DDL while DML operations continue to run uninterrupted on the partition that is being moved ▫ Global indexes are maintained during the move partition, so a manual index rebuild is no longer required ▫ With the ONLINE clause, the move operation waits for the transaction to be finished (commit or rollback) and is then executed

Oracle 12c 12c Partitioning New Features
Partition Maintenance Operations on Multiple Partitions ▫ Partition maintenance operations can be performed on multiple partitions as part of a single partition maintenance operation (one atomic operation) ▫ Simplifies application development and leads to more efficient partition maintenance using less system resources ▫ For example: ALTER TABLE table_name ADD PARTITION partition_name…, PARTITION partition_name…, PARTITION partition_name…;

Oracle 12c 12c Partitioning New Features
Asynchronous Global Index Maintenance for DROP and TRUNCATE Partition ▫ Global index maintenance is decoupled from DROP and TRUNCATE partition maintenance operations without rendering a global index unusable ▫ Index maintenance is done asynchronously and can be delayed to a later point in time

Oracle 12c 12c Partitioning New Features
Interval Reference Partitioning ▫ Referenced partitioned table leverages interval partitioning as the top partitioning strategy ▫ Enhances Oracle’s partitioning capabilities to model the database schema according to real business needs CREATE TABLE parent_table_name (column_name…, column_name…, …) PARTITION BY RANGE (column_name) INTERVAL(n)…; CREATE TABLE child_table_name (column_name…, column_name…, FOREIGN KEY…) PARTITION BY REFERENCE (fk_constraint_name)…;

Multitenant Database Architecture

Multitenant Architecture: Benefits
Operate multiple databases in a centrally managed platform
No application changes
Fast and easy provisioning
Ensures full backwards compatibility with non-CDBs
Fully operates with RAC and Data Guard
Supported by Enterprise Manager
Allows central management and administration of multiple databases (backups, patching, upgrades)

Some Stuff to Read on the Web
Introduction to a Multitenant Environment with Tom Kyte (video) https://www.youtube.com/watch?v=2MrouEW9j88


Multitenant: Create and Configure a Pluggable Database (PDB)

http://www.oracle-base.com/articles/12c/multitenant-create-and-configure-pluggable-database-12cr1.php


Oracle Multitenant (Pluggable Database) White Paper

https://blogs.oracle.com/UPGRADE/entry/oracle_multitenant_pluggable_database_white


Oracle Database In-Memory

http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html


Oracle Database In-Memory Data Sheet

http://www.oracle.com/technetwork/database/options/database-in-memory-ds-2210927.pdf


Oracle Database 12c Consolidation Best Practices

http://www.oracle.com/us/products/database/database-private-cloud-wp-360048.pdf

Some Stuff to Read on the Web
Oracle 12c Articles

http://www.oracle-base.com/articles/12c/articles-12c.php


Oracle Database 12c New Features – Parts 1 to 4

http://allthingsoracle.com/oracle-database-12c-new-features-part-i/ http://allthingsoracle.com/oracle-database-12c-new-features-part-2/ http://allthingsoracle.com/oracle-database-12c-new-features-part-3/ http://allthingsoracle.com/oracle-database-12c-new-features-part-4/


Top 12 New Features of Oracle 12c http://www.orafaq.com/node/2822


Ask Tom: on Oracle Database 12c, Part 1 and 2 http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html http://www.oracle.com/technetwork/issue-archive/2013/13-nov/o63asktom-2034271.html

Some Stuff to Read on the Web
Oracle Database 12c Query Optimization

http://www.oracle.com/technetwork/database/bi-datawarehousing/dbbi-tech-info-optmztn-092214.html


Adaptive Joins

https://blogs.oracle.com/optimizer/entry/what_s_new_in_12c


Tom Kyte: 12 Features of Oracle Database 12c (video) http://bcove.me/n7i11lei


Adaptive Execution Plans with Tom Kyte (video) http://youtu.be/9o9iuxNBciQ


Optimizer with Oracle Database 12c (Oracle White Paper)

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf

Thank You! Please fill out the feedback form Ami Aharonovich Oracle ACE & OCP [email protected]