Database Reference
In-Depth Information
Partitioning
Partitioning is one of the most important tools for Information Lifecycle Management (ILM) in the Oracle database
as it allows the administrator and application developer to group data in a heap table into more manageable, smaller
pieces. Implemented correctly the effect of using partitioning can be felt immediately, and is usually a very positive
one. A lot of the data in transactional systems is time based in one way or another. Usually, the most recently inserted
data is what the application needs to access most. In trading systems for example this would be the current hour, day,
or week depending on the granularity and volume of data to store and process. Recent data is often accessed and
modified, therefore having the highest demand for high quality storage. This low latency/high bandwidth storage
comes at premium cost, which is why older data that has cooled off a little is moved to lower-cost storage. Depending
on the age of the data and retention period mandated by the auditors this process could be repeated a number of
times until the data is on tertiary storage, maybe even in a platform-neutral format for really long-term archiving.
Many developers have already made good use of the Oracle Partitioning option to partition large tables into
range partitions stretching the before mentioned intervals. For administrators though moving partitions to lower tier
storage required the implementation of the change during a maintenance window, where partitions for a table have
been moved (and optionally compressed) to a different storage tier.
Automatic Data Optimization in Oracle 12c
Automatic Data Optimization (ADO) is based on access tracking on different segments of the database (the feature
requires a separate license). A segment in this context can be a table, or a (sub)partition.
Note
aDO is not supported for pluggable databases. But aDO is so incredibly useful that you should know about it
anyway.
The alter table and create table statements have been enhanced with new syntax allowing you to define ILM
policies once the tracking has been enabled. The initialization parameter heat_map controls the access tracking and it
has to be set system wide. Once the heat map tracking is enabled information about segment access is recorded in the
dictionary. Live tracking information is found in v$heat_map_segment . That view provides information about reads
and writes as well about as full- and lookup scans. A number of DBA%-views exist as well which can be queried for
access to segments.
One common use case of data lifecycle management is to compress “cold” data. The next example does exactly
this. The implemented strategy is as follows:
Access tracking is globally enabled by setting heat_map to “on.”
1.
2.
A new ILM policy is attached to the table, stating that the cold data should be compressed.
Thinking about the technology ADO makes the most sense with range-partitioned data based on a date column
in the context of Information Life Cycle Management. Hopefully the application has been designed with that in
mind allowing the optimizer to perform runtime partition pruning. Partitioning for performance and manageability
sometimes are conflicting goals.
To demonstrate the new lifecycle management technology the following demo has been created. A table
containing 100,000 is range partitioned on a date.
create table t1 (
d date,
vc1 varchar2(100),
 
 
Search WWH ::




Custom Search