Re: Hash Partition in OLTP with VPD

From: Job Miller <jobmiller_at_yahoo.com>
Date: Tue, 7 May 2013 05:35:00 -0700 (PDT)
Message-ID: <1367930100.1760.YahooMailNeo_at_web126104.mail.ne1.yahoo.com>

The documentation is this regard is pretty good. http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#CEGECIGF

7 Using Partitioning in an Online Transaction Processing Environment

I love the fact that they added these few paragraphs about the benefits and consequences of partitioning when primary access path is an index access The bold is my emphasis.  

Karth - Bullet #3 reiterates what Ls Cheng said:

In regards to VPD, does the VPD policy generate a where clause that uses the hash partitioning key?    If not, I'd hope the standard non-VPD generated predicates uses the partitioning key, otherwise you'll need to refer to bullet #2 below.

• 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.

  ---

 From: Ls Cheng <exriscer_at_gmail.com>
To: keyantech_at_gmail.com
Cc: Oracle Mailinglist <oracle-l_at_freelists.org> Sent: Tuesday, May 7, 2013 8:01 AM
Subject: Re: Hash Partition in OLTP with VPD  

Hi
I have used hash partition in OLTP system, it's main purpose is to reduce access contention, specially indexes. Use global indexes and not too many partitions (start with 4).

Regards

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 07 2013 - 14:35:00 CEST