Re: Oracle Partitioning
Date: Tue, 29 Oct 2013 04:39:10 -0700 (PDT)
Message-ID: <1383046750.55201.YahooMailNeo_at_web126104.mail.ne1.yahoo.com>
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_at_gcrta.org> To: oracle-l-freelists <oracle-l_at_freelists.org>; oracle-db-l <oracle-db-l_at_Groups.ITtoolbox.com> 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
.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 29 2013 - 12:39:10 CET