Re: Oracle Partitioning
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: "JBECKSTROM@xxxxxxxxx" <JBECKSTROM@xxxxxxxxx>, oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>, oracle-db-l <oracle-db-l@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 29 Oct 2013 04:39:10 -0700 (PDT)
You should read this section of the docs specifically that talks about
partitioning for OLTP.
http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_oltp.htm#CEGEGHGE
The following are benefits of partitioning for OLTP environments:
* Support for bigger databases
Backup and recovery, as part of a high availability strategy, can be performed
on a low level of granularity to efficiently manage the size of the database.
OLTP systems usually remain online during backups and users may continue to
access the system while the backup is running. The backup process should not
introduce major performance degradation for the online users.
Partitioning helps to reduce the space requirements for the OLTP system because
part of a database object can be stored compressed while other parts can remain
uncompressed. Update transactions against uncompressed rows are more efficient
than updates on compressed data.
Partitioning can store data transparently on different storage tiers to lower
the cost of retaining vast amounts of data.
* Partition maintenance operations for data maintenance (instead of DML)
For data maintenance operations (purging being the most common operation), you
can leverage partition maintenance operations with the Oracle Database
capability of online index maintenance. A partition management operation
generates less redo than the equivalent DML operations.
* Potential higher concurrency through elimination of hot spots
A common scenario for OLTP environments is to have monotonically increasing
index values that are used to enforce primary key constraints, thus creating
areas of high concurrency and potential contention: every new insert tries to
update the same set of index blocks. Partitioned indexes, in particular hash
partitioned indexes, can help alleviate this situation.
Performance
Performance in OLTP environments heavily relies on efficient index access, thus
the choice of the most appropriate index strategy becomes crucial. The
following section discusses best practices for deciding whether to partition
indexes in an OLTP environment.
Deciding Whether to Partition Indexes
Due to the selectivity of queries and high concurrency of OLTP applications,
the choice of the right index strategy is indisputably an important decisions
for the use of partitioning in an OLTP environment. The following basic rules
explain the main benefits and trade-offs for the various possible index
structures:
* A nonpartitioned index, while larger than individual partitioned
index segments, always leads to a single index probe (or scan) if an index
access path is chosen; there is only one segment for a table. The data access
time and number of blocks being accessed are identical for both a partitioned
and a nonpartitioned table.
A nonpartitioned index does not provide partition autonomy and requires an
index maintenance operation for every partition maintenance operation that
affects rowids (for example, drop, truncate, move, merge, coalesce, or split
operations).
* With partitioned indexes, there are always multiple segments.
Whenever Oracle Database cannot prune down to a single index segment, the
database has to access multiple segments. This potentially leads to higher I/O
requirements (n index segment probes compared with one probe for a
nonpartitioned index) and can have an impact (measurable or not) on the
run-time performance. This is true for all partitioned indexes.
Partitioned indexes can either be local partitioned indexes or global
partitioned indexes. Local partitioned indexes always inherit the partitioning
key from the table and are fully aligned with the table partitions.
Consequently, any kind of partition maintenance operation requires little to no
index maintenance work. For example, dropping or truncating a partition does
not incur any measurable overhead for index maintenance; the local index
partitions are either dropped or truncated.
Partitioned indexes that are not aligned with the table are called global
partitioned indexes. Unlike local indexes, there is no relation between a table
and an index partition. Global partitioned indexes give the flexibility to
choose a partitioning key that is optimal for an efficient partition index
access. Partition maintenance operations normally affect more (if not all)
partitions of a global partitioned index, depending on the operation and
partitioning key of the index.
* Under some circumstances, having multiple segments for an index can
be beneficial for performance. It is very common in OLTP environments to use
sequences to create artificial keys. Consequently, you create key values that
are monotonically increasing, which results in many insertion processes
competing for the same index blocks. Introducing a global partitioned index
(for example, using global hash partitioning on the key column) can alleviate
this situation. If you have, for example, four hash partitions for such an
index, then you now have four index segments into which you are inserting data,
reducing the concurrency on these segments by a factor of four for the
insertion processes.
With less contention, the application can support a larger user population.
Example 7-1 shows the creation of a unique index on the order_id column of
theorders_oltp table. The order_id in the OLTP application is filled using a
sequence number. The unique index uses hash partitioning to reduce contention
for the monotonically increasing order_id values. The unique key is then used
to create the primary key constraint.
If you are doing single row lookups via an index the majority of the time,
partitioning will not help performance of the query.
If you are lucky, it won't screw up the performance. if you partition
incorrectly, what was a couple i/o to find a row, could become a couple i/o *
the number of partitions in your table!
Chapter 3 talks about the benefits in general. (Not just performance)
http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_avail.htm#BJEIEDIA
________________________________
From: Jeffrey Beckstrom <JBECKSTROM@xxxxxxxxx>
To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>; oracle-db-l
<oracle-db-l@xxxxxxxxxxxxxxxxxxxx>
Sent: Monday, October 28, 2013 9:58 AM
Subject: Oracle Partitioning
We are looking into the possibility of licensing the Partitioning option. My
understanding of partitioning is that it only helps when the optimizer can
eliminate partitions. Doesn't that mean that it is primarily of benefit when
accessing the tables via full table scans? In that case, the optimizer could
eliminate partitions from scanning. If, however, you are accessing the data
via an index\, would there be any benefit?
Is my understanding of this correct?
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
Information Systems
1240 W. 6th Street
Cleveland, Ohio 44113
.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
