rebalancing partitions
From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Thu, 21 Jun 2012 10:31:14 -0400
Message-ID: <CAE-dsOJX+rjNQLsvSyx_jrQ3vaqUDukckZzpw6GKKRJfxXRHfQ_at_mail.gmail.com>
Oracle 10.2.0.5.7
if i have a table that is range partitions with 10 ranges. It has local indexes. If I want to add hash sub-partitions, how much risk is there that query plans will change? We have a vast number of queries, so we can re-test a large number of them, but a full regression test may not be practical with all the different scenarios. The system is primarily an OLTP. This table is about 70 gbs and growing. We are getting increased reporting requirements. Parallel works well with partitioning, but I want to look at breaking up the partitions into smaller chunks to better take advantage of parallel processing for reports that are increasing in complexity.
Date: Thu, 21 Jun 2012 10:31:14 -0400
Message-ID: <CAE-dsOJX+rjNQLsvSyx_jrQ3vaqUDukckZzpw6GKKRJfxXRHfQ_at_mail.gmail.com>
Oracle 10.2.0.5.7
if i have a table that is range partitions with 10 ranges. It has local indexes. If I want to add hash sub-partitions, how much risk is there that query plans will change? We have a vast number of queries, so we can re-test a large number of them, but a full regression test may not be practical with all the different scenarios. The system is primarily an OLTP. This table is about 70 gbs and growing. We are getting increased reporting requirements. Parallel works well with partitioning, but I want to look at breaking up the partitions into smaller chunks to better take advantage of parallel processing for reports that are increasing in complexity.
This is 17 year old database with a vast amount of existing code. It is extremely high volumes. Peak OLTP rates are in the 100s of thousands a minute and we have some queries that need to have sub-second response time. So any change like this needs to be investigated). I know about parallel and increased cpu usage. These are off hours reports.
Before I move forward with this, I am trying to get some background info to figure out a level of effort and risk.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 21 2012 - 09:31:14 CDT