Re: Hash Partition in OLTP with VPD
- From: Vasu <vasudevanr@xxxxxxxxx>
- To: keyantech@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 8 May 2013 16:43:45 -0500
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@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
