oracle database 12c new features - wkss - v1
Post on 26-Nov-2015
69 Views
Preview:
DESCRIPTION
TRANSCRIPT
Oracle Database 12c(New Features)
Riaz Ahmad21st-November-2013 1
Agenda Items
2
• History of Oracle• An introduction to
• Traditional, Grid & Cloud Computing
• Oracle Database 12c – New Features• Summary• References• Q&A Session
History of Oracle
3
Source: http://en.wikipedia.org/wiki/Oracle_Corporationhttp://www.dba-oracle.com/t_history_oracle.htm
Project Oracle 1977• Larry Ellison, Bob Miner, Ed Oates and Bruce Scott• Client C.I.A • Company was dubbed "Systems Development Labs", or SDL• 1978 SDL was renamed Relational Software Inc (RSI)
Oracle 2 1979 • Built using PDP-11 assembler language• Customers USA Air Force & CIA• In1982 RSI renamed Oracle Systems Corporation
Oracle 3 1983 • Built using C language• Could run in mainframes, minicomputers, and PCs – or any hardware
with a C compiler
History of Oracle
4
Oracle 4 1984• Support for reading consistency• Export/import utilities• Report Writer
Oracle 5 1985
• Network connectivity• Clustering technology• Security features like auditing• Distributed queries• SQL*Plus, a tool that offers ad hoc data access and report writing• SQL*Forms, an application generator and runtime system
Oracle 6 1988
• PL/SQL language• Hot backup capability• Row level locking• Oracle Parallel Server was introduced in Oracle version 6.2
Source: http://en.wikipedia.org/wiki/Oracle_Corporationhttp://www.dba-oracle.com/t_history_oracle.htm
History of Oracle
5
Oracle 7 1992• Security, administration, development, and performance• Stored procedures, triggers, Roles • Read-only table spaces• Dynamic SQL• Support for declarative referential integrity• All kinds of data types; including video, color images, sounds and spatial data.
Oracle 8 1997• Java• HTML • OLTP and support for terabytes of data
Oracle 8i 1998• the i stands for internet• XML SUPPORT• Temporary Tables (Session and Transaction level)• FBI (Function Based Indexes)• Analytic Functions• SKIP LOCKED Clause
Source: http://en.wikipedia.org/wiki/Oracle_Corporationhttp://www.dba-oracle.com/t_history_oracle.htm
History of Oracle
6
Oracle 9i 2001• Explicitly Named Indexes On Keys• MERGE Statement• External Tables• Multi-table Inserts• Table compression that reduced the size of tables by 3 to 10 times
Oracle 10g 2003• the g stands for grid, • Grid Computing technology (share hardware resources)• 1st Oracle version to support 64-bit on Linux
Oracle 11g 2007
• Administrative features• Case sensitive passwords• Online Patching• Oracle Database Replay
• Virtual Column Partitioning
Source: http://en.wikipedia.org/wiki/Oracle_Corporationhttp://www.dba-oracle.com/t_history_oracle.htm
History of Oracle
7
Audit Vault Database Vault Grid Computing Self Managing Database XML Database Oracle Data Guard Real Application Clusters Flashback Query Virtual Private Database Built in Java VM Partitioning Support Built in Messaging Object Relational Support Multimedia Support Data Warehousing Optimizations Parallel Operations Distributed SQL & Transaction Support Cluster and MPP Support Multi-version Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation
1977 2012
36 years of sustained innovation …
Traditional Computing Complicated and expensive Need a whole team of experts to install,
configure, test, run, secure, and update them. Grid Computing
Where more than one computer coordinates to solve a problem together.
Grid technologies have evolved in the direction of service-orientation.
Traditional, Grid & Cloud Computing
8
Cloud Computing Internet-based computing Sharing computing
resources rather than having
Local servers or personal
devices to handle the
applications.
Traditional, Grid & Cloud Computing
9
Oracle Database 12c
10
Next-generation database
Organizations embrace the cloud, they seek technologies that will transform business and improve their overall operational agility and effectiveness.
Oracle Database 12c introduces a new multitenant architecture.
A multitenant container database (CDB) is an Oracle database that includes zero, one, or many user-created PDBs (plugable databases).
Oracle Database 12c
11
Oracle Database 12c
12
Oracle 12c – Extended Data Types
13
Feature # 1 : Extended Data Types
Data Type Before Now
VARCHAR2 4,000 Bytes 32,767 Bytes
NVARCHAR2 4,000 Bytes 32,767 Bytes
RAW 2,000 Bytes 32,767 Bytes
• Optional feature, controlled using MAX_STRING_SIZE initialization parameter• One-way operation
Oracle 12c – Auto-Increments
14
Feature # 2 : Auto-IncrementsColumns which will auto increment its values whenever a new row is added. MySQL - AUTO_INCREMENT column attribute is used.
MSSQL - IDENTITY column attribute is used.
DB2 - GENERATED ALWAYS AS IDENTITY column attribute.
Teradata - GENERATED ALWAYS AS IDENTITY column attribute.
Sybase - IDENTITY column attribute is used.
Informix - SERIAL data type is used for auto-increment columns.
Oracle – Two alternatives are provided with 12c
Oracle 12c – Auto-Increments
15
1. Identity columnsSyntax:
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]
Example:
CREATE TABLE Test_Tab1 (
id NUMBER GENERATED ALWAYS AS IDENTITY,
description VARCHAR2(30)
);
2. Sequence Pseudo-columnsSyntax:
DEFAULT sequence. NEXTVAL
Example:
CREATE TABLE identity_test_tab (
id NUMBER DEFAULT DbSequence.NEXTVAL,
description VARCHAR2(30)
);
Oracle 12c – Auto-Increments
16
Impact on Performance– Trigger-based test performs much worse than the others.– The direct use of a sequence and the 12c identity column give
comparable results, which are typically an order of magnitude faster than using a trigger to populate the ID column.
Oracle 12c – Implicit Statement Results
17
Feature # 3 : Implicit Statement Results
Background:
T-SQL Developer: How do I pass results out of a stored procedure?
Oracle Developer: You use an out parameter.
T-SQL Developer: But I want to return a result set.
Oracle Developer: No problem, make the out parameter a ref cursor and you're laughing.
T-SQL Developer: So I have to define out parameters for each of the result sets I want to pass out?
Oracle Developer: Yes.
T-SQL Developer: Oh man! Oracle Sucks!
* Transact-SQL allows implicit returns of results from queries
Oracle 12c – Implicit Statement Results
18
Example:CREATE OR REPLACE PROCEDURE get_my_results (p_id IN NUMBER DEFAULT NULL)
AS
l_cursor_1 SYS_REFCURSOR;
BEGIN
OPEN l_cursor_1 FOR
SELECT description, created_date
FROM t1
WHERE id = p_id;
DBMS_SQL.RETURN_RESULT(l_cursor_1);
END; SQL> EXEC get_my_results(1);
PL/SQL procedure successfully completed.
ResultSet #1
DESCRIPTION CREATED_DATE
------------------------------ --------------------
The value 1 06-JUL-2013 21:19:45
1 row selected.
Oracle 12c – Default Values
19
Feature # 4 : Default Values Default Values using Sequences
CREATE <table_name> ( Col1 NUMBER DEFAULT <sequence_name>.NEXTVAL, description VARCHAR2(30) );
Default Values on Explicit NULLs
CREATE <table_name> ( Col1 NUMBER DEFAULT <sequence_name>.NEXTVAL, Col2 NUMBER DEFAULT ON NULL <sequence_name>.NEXTVAL, description VARCHAR2(30) );
Oracle 12c – Default Values
20
Metadata-Only DEFAULT Values• Prior to Oracle 11g, adding a new column to an existing table
required all rows in that table to be modified to add the new column.
• 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
• Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. This represents both a space saving and performance improvement.
Oracle 12c – Truncate Table CASCADE
21
Feature # 5 : Truncate Table CASCADE Truncates the records in the master table and
automatically initiates recursive truncate on child tables too.
Truncation is subject to foreign key reference as DELETE ON CASCADE.
An ORA-14705 exception will be thrown if no ON DELETE CASCADE option is defined with the foreign keys of the child tables.
SQL> TRUNCATE TABLE <table_name> CASCADE; SQL> TRUNCATE TABLE <table_name> PARTITION <partition_name> CASCADE;
Oracle 12c – ROW limiting for TOP-N
22
Feature # 6 : ROW limiting for TOP-N result There are various indirect approaches/methods exist to
fetch Top-N query results for top/bottom rows in the previous releases.
Straight forward in 12c with the new FETCH FIRST|NEXT|PERCENT clauses.
Requirement # 1. retrieve top 10 salaries from EMP table.SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY;
Requirement # 2. 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;
Oracle 12c – Session level Sequences
23
Feature # 7 : Session level Sequences Most useful and suitable on global temporary tables
that have session level existence. Generates a unique range of values that are limited
within the session, not across the sessions. Once the session ends, the state of the session
sequences also goes. The CACHE, NOCACHE, ORDER or NOORDER claus
es are ignored for SESSION level sequences.
Syntax :
SQL> CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION;
Oracle 12c – Function Optimization in SQL
24
Feature # 8 : Function Optimization in SQL Function itself can be defined inside the SQL
statement using the WITH clause.
Syntax:
WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS BEGIN
RETURN p_id; END; SELECT with_function(id) FROM t1 WHERE rownum = 1 /
Oracle 12c – Function Optimization in SQL
25
Syntax:
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS PRAGMA UDF; BEGIN
RETURN p_id; END; /
Use of UDF pragma to the program unit, which tells the compiler that the function will be used primarily in SQL statements.
Summary
26
History of Oracle (from Oracle Projects to Oracle 11g) Discussed about Traditional, Grid & Cloud Computing Discussed about Oracle Database 12c new features
1. Extended Data types
2. Auto-increment Columns / Identity Columns
3. Implicit Statement Results
4. Defalut Values for Table Columns– Default values using Sequences– Default values On Explicit NULLs– Metadata-Only Default Values
5. Truncate Table Cascade
6. Row limiting for Top-N result queries
7. Session Level Sequences
8. Function Optimization in SQL (With Clause, UDF Pragma)
References
27
Oracle Press Release, Oracle Announces General Availability of Oracle Database 12c, the First Database Designed for the Cloud, July 1, 2013, http://www.oracle.com/us/corporate/press/1967380
The History of Oracle, Oracle Tips by Paulo Ferreira Portugal, April 8, 2009, http://www.dba-oracle.com/t_history_oracle.htm
Oracle Database 12c Release 1: Miscellaneous Articles, http://www.oracle-base.com/articles/12c/articles-12c.php.
Oracle Database Documentation Library, http://www.oracle.com/pls/db121/homepage
28
top related