Tom’s Top 12 Things About the
Latest Generation of Database
Technology
My bullets don’t quite match Tom’s,
which is why I have more than 12
things listed.
Functions (and procedures used
within those functions) can be defined
in the WITH clause. Performance
boost compared to regular unit
defintion. Pragma to allow regular
functions to benefit from these
performance benefits.
Default value of column can use a
sequence.nextval.
Identity columns : Multiple levels of
control of how it is used. Can use
simple or more complex syntax.
Metadata only default of optional
columns. Previous versions this was
possible only for mandatory columns.
VARCHAR2(32767) in the database.
Less than 4K is stored inline. More
than 4K is stored out of line, similar to
LOB, but simpler. Not available by
default.
Top-N now using Row limiting clause
eg. “OFFSET 10 ROWS FETCH FIRST 10
ROWS ONLY”. Similar to mySQL
syntax.
Row pattern matching. Quite a lot of
new analytic syntax here.
Partitioning Improvements:
- Asynchronous Global Index
maintenance for DROP and
TRUNCATE. Command returns
instantly, but index cleanup happens
later.
- Cascade for TRUNCATE and
EXCHANGE partition.
- Multiple partition operations in a
single DDL
- Online move of a partition(without
DBMS_REDEFINTIION).
- Interval+ Reference Partitioning.
Adaptive Execution Plans:
- If the optimizer notices the
cardinality is not what is expected, so
the current plan is not optimal, it can
alter subsequent plan operations to
take allow for the differences between
the estimated and actual cardinalities.
- The stats gathered during this
process are persisted as Adaptive
Statistics, so future decisions can
benefit from this.
- You will see STATISTICS COLLECTOR
steps in the SQL Trace. Can make the
trace harder to read as it can contain
information about the expected plan
and the actual plan.
Enhanced Statistics:
- Some dynamicsampling operations
are persistent, so they are not lost
when the SQL is aged out.
- Hybrid histograms. When the
number of distinct values is greater
than 254, “almost popular” values can
get “lost” in the mix. A single bucket
can now store the popularity of than
value, effectively increasing the
number of buckets, without actually
increasing it.
- Possible the max number of buckets
can be increased based on a
parameter. (demo grounds)
- Statistics gathered during loads.
CTAS and INSERT … SELECT
automatically compute stats.
- Global temporary tables can have
“session private statistics”. Previously,
we had one-size-fits-all.
Temporary Undo (ALTER SESSION SET
temp_undo_enabled=true):
- UNDOfor temporary tables can now
be managed in TEMP, rather than the
regular UNDO tablespace.
- Reduces contents of regular UNDO,
allowing better flashback operations.
- Reduces the size of redo associated
with recovering the regular UNDO
tablespace.
Data Optimization:
- Information Lifecycle Management:
Uses heat map. Colder data is
compressed and moved to lower tier
storage. Controlled by declarative DDL
policy.
Transaction Guard:
- If a failure happens, your application
may not know the actual status of a
transaction. If it was successful,
issuing it again could cause a
duplication transaction.
- In these cases, you can mark a
transaction with an “unknown” state
(as far as the application is
concerned) as failed, so even though
they may have been successful, it will
never be considered, or recovered.
You’ve guaranteed the outcome.
Pluggable database:
- Oracle provided metadata and data
is kept in the container database
(CDB).
- Usermetadata and data is kept in
the plugable database (PDB) .
- One container can have multiple
plugable databases.
- No namespace clashes. Allows public
synonyms and database links at the
PDB level, rather than the CBD level.
- Cloning is quick and simple as only
user metadata and data needs to be
cloned.
- Upgrades have the potential to just
unplug from old version (12cR1) to
new version (2cR2).
- Reduce total resource usage is
reduced on lower use databases.
|