11g new features for Developer
Oracle 11g R2
•
Results Cache Improvements
•
New Analytic Functions
•
XML Enhancements
•
Java Enhancements
•
Pro*C/Pro*COBOL Enhancements
•
Edition-Based
Redefinition (EBR)
Oracle 11g Preview
•
iSqlPlus and
SQLPLUSW gone (SQL*Plus & SQL Developer still there)
•
Virtual Columns
•
XML DB Binary
XMLTYPE
•
SQL Pivot/UnPivot
•
REGEXP_COUNT
•
PL/SQL compiler
enhancement
•
Assign sequence
numbers in PL/SQL
•
PL/SQL CONTINUE
•
Trigger
improvements
•
New JDBC driver
support Java 5 (1.5) & 6
New SQL Functions
•
New functions have also been added to
Oracle 11g including:
CUBE_TABLE Extracts two-dimensional table from
a cube or dimension
REGEXP_COUNT Count Occurrences of string
XMLCAST Cast XML data to SQL datatype
XMLEXISTS Determine if XQuery returns values
XMLDIFF Used to compare two XMLType documents
XMLPATCH Used to patch an XMLType document
Next-Gen. LOB: Secure file
•
Oracle 11g provides a new, more-secure,
faster mechanism for storing Large Objects (e.g. XMLType data)
•
LOB column specifications in CREATE TABLE
or ALTER TABLE include STORE AS SECUREFILE
•
SECUREFILE provides compression and
encryption for Large Objects (LOBs)
•
Oracle 11g will detect duplicate LOB data
and conserve space by only storing one copy ("de-duplication" if
SECUREFILE is specified).
•
PL/SQL packages and OCI functions have
been added to take advantage of SECUREFILE LOBs
•
SECUREFILE lobs provide higher performance
through reduced size and resource use.
XML Indexes
•
Replaces CTXSYS.CTXXPATH indexes
•
XML-specific index type, indexes document
XML structure
•
Designed to improve indexing unstructured
and hybrid XML
•
Determines XPath expressions for a
document's XML tags
•
Indexes singleton (scalar) nodes and items
that occur multiple times
•
XMLIndex record document child,
descendant, and attribute axes (hierarchy) information
•
XMLIndex is be design general (like CTXXPATH)
rather than specific like B-tree indexes
•
XMLIndex applies to all possible XPath
targeting of a document
•
XMLIndex may be used for XMLQuery,
XMLTable, XMLExists, XMLCast, extract, extractValue, and existsNode
•
XMLIndex helps anywhere in the query, not
just in the WHERE clause
Creating XMLIndex
•
The syntax to create an XMLIndex looks a
little different from non-XML indexes; it is made up of three parts:
•
Path index
Indexes XML tags and identifies Document fragments
•
Order index Indexes the hierarchy of nodes
•
Value index Values to match WHERE clauses
(May be exact match or range)
•
XMLIndex uses a “Path Table” to store the
various node paths in an XML document; if not specified in the CREATE INDEX
statement Oracle will generate a name for you
CREATE INDEXpo_xmlindex_ix
ON po_clob (OBJECT_VALUE)
INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATH TABLE
my_path_table');
Introducing Virtual Columns
•
Beginning with Oracle 11g tables may now
include virtual columns (dynamic values; not stored)
•
Virtual columns obtain their value by
evaluating an expression that might use:
Ø Columns
from the same table
Ø Constants
Ø Function
calls (user-defined functions or SQL functions)
•
Virtual columns might be used to:
Ø Eliminate
some views
Ø Control
table partitioning (DBA stuff)
Ø Manage
the new "binary" XMLType data
•
Virtual columns may be indexed!
Creating Virtual Column
CREATE TABLE NEWEMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
INCOME NUMBER(9,2)
GENERATED ALWAYS
AS
(NVL("SAL",0)+NVL("COMM",0))VIRTUAL,
DEPTNO NUMBER(2));
•
Datatype defaults if not specified (based
upon expression)
•
Expression result appears as data in table
but is “generated always” (whether or not specified in table definition)
•
“ VIRTUAL” is not required, but adds
clarity
Adding Virtual
Columns:
alter table myemp add
(totpay
as
(nvl(sal,0)+nvl(comm,0)));
PIVOT/UNPIVOT
•
Oracle joins other vendors by adding the
PIVOT clause to the SELECT statement
•
Adding a PIVOT clause to a SELECT allows
rotation of rows into columns while performing aggregation to create
cross-tabulation queries
•
The PIVOT clause:
Ø Computes
aggregations (implicit GROUP BY of all columns not in PIVOT clause)
Ø Output
of all implicit grouping columns followed by new columns generated by PIVOT
•
UNPIVOT performs the same activity but
converts columns into ROWS (does not “undo” PIVOT)
•
Clever developers have used PL/SQL and/or
CASE to achieve PIVOT results before, but now it is part of Oracle's standard
SQL
PIVOT Example:
 |
select
* from
(select
job,deptno,income from newemp)query1
pivot
(avg(income)
for
deptno in (10 AS ACCOUNTING,
20
AS RESEARCH,
30
AS SALES))
order
by job;
 |
UNPIVOT Example:
 |
|||
 |
|||
select
* from pivot_emp_table
unpivot
include nulls
(avgpay
for dept in (ACCOUNTING,RESEARCH,SALES))
order
by job;
 |
Oracle 11g Read-Only Tables
•
Beginning with Oracle 11g the database
supports read-only table mode
 |
Alter
table myTable read only;
Alter table myTable read write;
•
When a table is in read only mode INSERT,
UPDATE, DELETE, and MERGE fail.
•
However, SELECT, CREATE INDEX, and other
commands that do not alter data are allowed.
Invisible Indexes
•
Sometimes the optimizer selects the wrong
index:
Ø Beginning
with Oracle 11g it is possible to make an index “invisible” to the optimizer
Ø Use
ALTER TABLE to make it visible/invisible
create
index mytab_ix on mytab(mykey) invisible
alter
intex mytab_ix invisible;
alter index mytab_ix visible;
Results Caching
•
Caching is nothing new to Oracle;
Oracle has cached data for a long time now
•
What’s new is the caching of results…
•
This is similar to how a Materialized View
works but is more-dynamic
•
New “result_cache” hint asks Oracle to
cache query results
select cust_last_name ||
', ' || cust_first_name cust_name
,cust_city
,prod_id
,count(*) nbr_sales from
sh.customers cust
join sh.sales sales
on cust.cust_id =
sales.cust_id where country_id = 52789
and prod_id in (120,126)
group by
cust_last_name,cust_first_name,cust_city,prod_id having count(*) > 10
order by
cust_name,nbr_sales;
•
This query was run three times in
succession with timing turned on; resulting timings were
– Elapsed: 00:00:00.67
– Elapsed: 00:00:00.46
– Elapsed: 00:00:00.37
PL/SQL Enhancements
•
Oracle 11g’s changes to PL/SQL are very
interesting to the developer:
Ø PL/SQL
has been improved to include all of the XMLType, BLOB, Regular Expression, and
other functionality added to SQL
Ø Improvements
have been made to the compiler
Ø New
PL/SQL data types
Ø Sequence
number use is easier
Ø “Continue”
added for loop control
Ø CALL
syntax has improved
Compiler Enhancement
•
In previous releases, the PL/SQL compiler
required a standalone “C” compiler
•
Oracle 11g now provides a native compiler
for PL/SQL eliminating the need for a separate compiler
ALTER PROCEDURE my_proc
COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
ALTER PROCEDURE my_proc
COMPILE PLSQL_CODE_TYPE=INTERPRETED
REUSE SETTINGS; ALTER
SESSION SET
PLSQL_CODE_TYPE=NATIVE;
ALTER SESSION SET PLSQL_CODE_TYPE=INTERPRETED;
Compound Triggers
•
Compound triggers allow the same code to
be shared across timing points (previously accomplished using packages most of
the time)
•
Compound triggers have unique declaration
and code sections for timing point
•
All parts of a compound trigger share a
common state that is initiated when the triggering statement starts and is
destroyed when the triggering statement completes (even if an error occurs)
Compound
Trigger Timing:
•
If multiple compound triggers exist for
the same table; they fire together:
Ø All
before statement code fires first
Ø All
before row code fires next
Ø All
after row code fires next
Ø All
after statement code finishes
•
The sequence of trigger execution can be
controlled only using the FOLLOWS clause
New PL/SQL Datatypes
•
Oracle 11g adds three new PL/SQL
datatypes:
Simple_integer, Simple_float,
Simple_double
Ø The
three new datatypes take advantage of native compilation features providing
faster arithmetic via direct hardware implementation
Ø SIMPLE_INTEGER
provides a binary integer that is neither checked for nulls nor overflows
Ø SIMPLE_INTEGER
values may range from -2147483648 to 2147483647 and is always NOT NULL
Ø Likewise,
SIMPLE_FLOAT and SIMPLE_DOUBLE provide floating point without null or overflow
checks
CALL with Mixed Parameters
•
PL/SQL allows function and procedure
parameters to be specified in two ways; by position and by name
•
With Oracle 11g SQL, parameter types may
now be mixed
•
Given the following function:
CREATE OR REPLACE
FUNCTION TEST_CALL
(inval1 IN NUMBER, inval2 IN
NUMBER,
inval3
IN NUMBER) RETURN NUMBER AS
BEGIN
RETURN
inval1 +inval2 +inval3;
END TEST_CALL;
The following
calls all now work:
test_call(vara,varb,varc)
test_call(inval3=>varc,inval1=>vara,inval2=>varb) test_call(vara,inval3=>varc,inval2=>varb)
Subprogram inlining in 11g
•
Oracle 11g has taken compiler optimisation
further, in particular with the concept of subprogram inlining. With subprogram
inlining, Oracle will replace a call to a subroutine (such as a function) with
the subroutine code itself during compilation. One of the benefits of this is
that we can continue to write well-structured, modular code without any
performance penalties. For SQL-intensive PL/SQL programs, the gains from inlining
might be marginal, but for procedural code, inlining might provide some greater
optimisation benefits, as we will see in this article.
Example:
•
We will begin with an extremely simple
example of subprogram inlining. In the following example, we request that
Oracle inlines function F. We do this with the new PRAGMA INLINE syntax as
follows.
DECLARE
n PLS_INTEGER;
FUNCTION f RETURN PLS_INTEGER IS
BEGIN
RETURN 10;
END f;
BEGIN
PRAGMA INLINE(f, 'YES');
n := f();
DBMS_OUTPUT.PUT_LINE(n);
END;
/
•
The PRAGMA INLINE syntax for a subprogram
can take either a 'YES' or 'NO' value and precedes the first call to the
subprogram. It will be effective for subsequent calls to the same subprogram
within a statement (unless a subsequent 'NO' pragma overrides it). In the
PL/SQL Language Reference, Oracle states:
When the INLINE pragma
immediately precedes one of the following statements, the pragma affects every
call to the specified subprogram in that statement:
Ø
Assignment
Ø
Call
Ø
Conditional
Ø
CASE
Ø
CONTINUE-WHEN
Ø
EXECUTE IMMEDIATE
Ø
EXIT-WHEN
Ø
LOOP
Ø
RETURN
•
There is a wide range of permutations for
the behaviour of the PRAGMA INLINE directive which can be found in the documentation
(a link is provided at the end of this article).
pls-00436 removed in oracle 11g
•
Since its release in Oracle 8i, there has
been a frustrating restriction with FORALL: the PLS-00436 "implementation
restriction". Readers who are familiar with FORALL will be aware of
PLS-00436. It is the implementation restriction that prevents us from accessing
specific record attributes within a FORALL DML construct. One of the first
things I check whenever there is a new major release of Oracle is whether this
restriction has been removed. With the release of 11g, it has (finally) and
this short article will demonstrate the techniques that are now available to
us.
Example:
•
Using the examples from the article
referenced above, we will perform a simple update on a column in EMP using
FORALL. This is for demonstration purposes only: under normal circumstances the
code would be sufficiently complex to warrant the use of PL/SQL over SQL in the
first place. The example is as follows.
DECLARE
TYPE emp_aat IS TABLE OF emp%ROWTYPE
INDEX BY PLS_INTEGER;
aa_emps emp_aat;
BEGIN
/* Fetch the data... */
SELECT * BULK COLLECT INTO aa_emps
FROM
emp;
/* Update the SAL column... */
FORALL i IN 1 .. aa_emps.COUNT
UPDATE emp
SET
sal = aa_emps(i).sal * 1.1
WHERE
empno = aa_emps(i).empno;
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SQL%ROWCOUNT) || ' rows
updated.'
);
END;
/
Implications for
inserts
•
The removal of the PLS-00436 restriction
simplifies several areas of PL/SQL FORALL coding, including inserts. As
discussed in the referenced background article, FORALL INSERTs that involve a
partial set of target columns can be achieved in previous versions using a
record-based insert into an in-line view over the target table (an 8i
alternative would be to have an array for each target column). The relaxation
of PLS-00436 removes the need for such coding techniques, however. We can now
use a simple VALUES () list, as we will see below.
•
In the following example, we will create a
copy of the EMP table and populate it with a subset of columns. First, we
create the table as follows.
CREATE TABLE emp_copy
AS
SELECT *
FROM
emp
WHERE ROWNUM < 1;
External table enhancements in 11g
•
This article introduces a small number of
new features for external tables in Oracle 11g. Read-only external tables were
introduced in Oracle 9i and are now commonplace in most database applications
that need to "import" flat-file data. Writeable external tables were
introduced in Oracle 10g (as part of the Data Pump family of utilities) and
they enable us to extract data from the database while at the same time creating
an external table to read the data back again. Both types of external table
have been enhanced in 11g, as we will see below.
This article covers the following new
features:
- support
for compressed data (read and write);
- support
for encrypted data (read and write); and
- Ability
to read from a pre-processed input data stream (available from 11.1.0.7).
Performance
impact: writing compressed data
By compressing
data, we expect our CPU consumption to increase (as data is compressed) but the
amount of file I/O to decrease (as fewer bytes of data are being written to
file). We might also expect the I/O savings to outweigh the CPU costs. To test
these assumptions, we will compare the relative performance of the compressed
external tables below. First, we will use a variation of Tom Kyte's RUNSTATS
utility to compare the overall resource consumption of compressed and
uncompressed external tables. Second, we will compare the CPU time taken by
each approach. In both cases, we will generate a file of 10 times the volume of
ALL_OBJECTS, beginning with the uncompressed version as follows (note that the
ALL_OBJECTS view data is cached in advance).
Collect enhancements in 11g
•
The COLLECT aggregate function was
introduced in Oracle 10g and enables us to aggregate data into a collection in
SQL. Oracle 11g Release 2 includes two enhancements to this function: the
ability to order and de-duplicate data as it is aggregated. In this article, we
will examine these new features and demonstrate a way to replicate their
functionality in 10g and 11g Release 1.
SQL> break on deptno
skip 1
SQL> SELECT deptno
, COLLECT(ename) AS emps
FROM emp
GROUP BY
deptno;
DEPTNO
EMPS
------------- ------------------------------------------------------------------------------------
10 SYSTPd/UcOeePQOrgQKjAAjgR1A==('CLARK', 'MILLER', 'KING')
20 SYSTPd/UcOeePQOrgQKjAAjgR1A==('SMITH', 'FORD', 'ADAMS', 'SCOTT',
'JONES')
30 SYSTPd/UcOeePQOrgQKjAAjgR1A==('ALLEN', 'JAMES', 'TURNER', 'BLAKE',
'MARTIN', 'WARD')
3 rows selected.
•
Using COLLECT, we have aggregated a single
collection per department as required but the nested table type is
system-generated and not very usable. By using our own collection type, we can
make this much more usable and flexible. First, we will create a general
collection type, as follows.
SQL> CREATE TYPE
varchar2_ntt AS TABLE OF VARCHAR2(4000);
/
Type created.
OTHER NEW PL/SQL FEATURES IN ORACLE DATABASE 11G
Functionality
language feature: Dynamic SQL completion
The execute
immediate can take a clob. DBMS_Sql.Parse() can take a clob. DBMS_Sql can
handle user-defined types in the select list and for binding. You can convert a
DBMS_Sql numeric cursor to a ref cursor (so that you can pass it back to a
database client or, when you know the select list at compile time, you can
fetch it into a record or bulk fetch it into a collection of records). You can
convert a ref cursor to a DBMS_Sql numeric cursor (so that you can describe
it). You now need DBMS_Sql only for “method 4” or for arcane requirements like
executing DDL across a database link.
Functionality
language feature: DBMS_Sql security
An attempt to
guess the numeric value of an opened (and parsed) DBMS_Sql cursor (so that you
can unscrupulously hijack it, rebind to see data you shouldn’t, and execute it
and fetch from it) now causes an error and disables DBMS_Sql for that session.
The binding and execution subprograms must be invoked by the same Current_User,
with the same roles, as invoked the parse. Optionally, via a new
DBMS_Sql.Parse() overload, the invocation of all DBMS_Sql subprograms can be
restricted in this way.
Functionality
language feature:
Fine-grained
access control for Utl_TCP, etc
It’s no longer
enough to have the Execute privilege on Utl_TCP and its cousins (Utl_HTTP,
Utl_SMTP, Utl_Mail, Utl_Inaddr). You must also have the specific privilege to
establish a TCP/IP connection to each node:port combination that you intend to
access. The fine grained privileges are granted by getting the DBA to create an
ACL (of users and roles), and to apply this to a port (range) on a node, using
the DBMS_Network_Acl_Admin package.
Functionality
language feature:
Create a disabled trigger;
specify trigger firing order
Use the disable
keyword in the create statement to avoid the risk that, if the trigger fails to
compile, DDL to the table will cause the ORA-04098 error. Use the follows
keyword in the create statement to control the mutual firing order for triggers
at the same timing point (e.g., before each row). This helps when triggers are
generated programmatically.
Functionality
language feature:
Invoke a specific
overridden ADT method
If you don’t know
what dynamic polymorphism is and don’t write hierarchies of ADTs with
overridden member subprograms, then this feature won’t interest you. Else, read
on. New syntax allows the implementation of an overriding subprogram to invoke
the member subprogram that it overrides that is defined in a specified
supertype. This was the motivating use case; but the same syntax can also be
used ordinarily to extend the dot notation that invokes an object’s method. The
plain dot notation invokes the method as overridden at this object’s type level
in the hierarchy; the extended notation allows you to invoke a particular
overridden method at any level in the hierarchy.
Usability language
feature:
The compound
trigger
A well known
paradigm uses a before statement trigger, a before each row or after each row
trigger, an after statement trigger, a package specification, and its body in
concert. The paradigm is useful when you want to “notice” activity for each row
the firing DML affects but act upon this only periodically. This might be to
improve efficiency by noticing auditing information, recording this in a
package global collection, and bulk inserting it into the audit table; or it
might be to avoid the mutating table error. The compound trigger is a single
database object that “feels” rather like a package body and lets to you
implement the same paradigm in one unit. It lets you implement a
“pseudo-procedure” for each of the four table timing points and declare state that
each of these pseudo-procedures can see. I called them pseudo-procedures
because they look very rather like procedures but they are named using
keywords. Like this:
before each row is
begin
...
end before each row
The state is
managed automatically to have the lifetime of the firing DML statement rather
than that of the session.
Usability language
feature:
Use
My_Sequence.Nextval in a PL/SQL expression
You no longer need
to say select My_Sequence.Nextval into v from Dual; The same goes for Currval.
Usability language
feature: The continue statement
PL/SQL catches up
with other languages. The continue has a familial relationship to exit. Both
can be written unconditionally or together with a boolean expression as the
argument of when; and both can specify the name of an enclosing loop. The
difference is that exit abandons the nominated loop but continue starts the
next iteration of that loop.
Usability language
feature: Named and mixed notation from SQL
PL/SQL programmers
know the value of invoking a subprogram that has a very large number of
defaulted formal parameters by writing
Interesting_Formal=>Interesting_Actual
only for those
formal parameters whose default they don’t want to accept. This value has been
unavailable when a PL/SQL function is invoked in a SQL statement. Now this
restriction is removed.
Usability language
feature:
“When others then
null” and other compile-time warnings
Naïve programmers
have a tendency to provide a when others then null exception handler for every
subprogram because they believe that it’s wrong to cause an error (yes, really
- read Tom Kyte on this topic). Of course, when a subprogram quietly returns
after suppressing an unknown error, and the caller blunders on in blissful
ignorance, then anything might happen — and debugging is a nightmare.
It would be very
labor intensive, and error-prone, to attempt an analysis of extant code to look
for this dangerous locution. Now, the compiler spots if a when others handler
does not unconditionally re-raise the exception or raise a new one; in this
case it issues PLW-06009. Other new warnings are implemented, for example
PLW-06006, as has been discussed above.
Usability language
feature: Regexp_Count() in SQL and PL/SQL
This is just what
its name implies. It would be tedious to write code to produce the same answer
without this new function.
Thank You.
Comments
Post a Comment