Re: Hash Partition in OLTP with VPD
Date: Wed, 8 May 2013 16:43:45 -0500
Message-ID: <CA+O6cLLY-x01eKPRFB+HH-Bh6d7qP9Uos4TihXTx5kWxMLY-Og_at_mail.gmail.com>
Karth,
1) You are trying to re-architect existing high transaction OLTP system , and have seen other partitioning type except Hash .
This is how I did.
1. Invest time in understanding various partitioning methods, features
and benefits .
2. Study the system/application, specifically data access pattern, OLTP
type and other batch queries . (sample common/often used SQLs).
With more analysis , and applying the facts from 1 and 2 , you will hit the
'Aaahaa' moment !
Don't stop there. Question your wisdom, test run your sample SQLs on your
model . Improvise it.
2) Even Tom Kyte says this : OLTP partitioning is challenging . Think thru your OLTP workflow (mostly covered by Global Indexes , optionally hash partitioned). Batch/reporting comes only next to it. Evaluate the gains in the long term , like ability to archive the data in the future , benefits of range scan for reporting etc . If you have DW , you won't bother much about reporting .
3) If most of the key/important tables are hash partitioned.. its likely that someone did partitioning without any clear objective. As you implicitly stated : Hash partitioning is the last resort when the table doesn't have any obvious keys suitable for range/list partitioning (and hash can be a sub-partition if required) . Oracle manual tells that.
4) VPD - As for I know, it adds additional "where col=<app.context>" to the query . It most probably doesn't come into your partition decision making.
When you re-partition your system, it is a resonable expectation that the old hash partition key will have at least one index , So no performance hit.
I work for a division of a telecom that says more is good. It took almost 2 years to design , test and migrate the critical tables. Sometimes, you can overcome tech.challenges more easily, than politics ! So, after all this..thought I have some bragging rights on the topic.
Good luck!
Cheers,
Vasu
Date: Mon, 6 May 2013 20:11:13 -0400
Subject: Hash Partition in OLTP with VPD
From: Karth Panchan <keyantech_at_gmail.com>
All
I am working on re-architect existing high transaction OLTP system facing
customers with 1Million users per hour.
It is on Oracle 11.2 Version on Enterprise Linux with 2 node RAC.
This system faced by around approx 8000 clients. Microsoft .NET web tier.
I am new to this place and in database, many tables are Hash partitioned using VPD.
I was debating myself, it would be better with none other Hash Partition. Not sure, it is because last 7 years I developed most of partition types except Hash.
Anyone have insight/experience with Hash on OLTP (or) with VPD?
Thanks in advance.
Karth
-- -Vasu -- http://www.freelists.org/webpage/oracle-lReceived on Wed May 08 2013 - 23:43:45 CEST