Re: sub-partition drop
Date: Sun, 11 Mar 2012 22:03:53 -0500
The main harm in unnecessary partitions is that if a query spans multiple subpartitions, it uses partition-level statistics. What might have been a better choice would be to have used LIST subpartition on col2, as it's significantly more flexible; I'd only use hash if I were looking to spread heavy,heavy insert load or get easy job parallelism.
That said, DBMS_REDEFINITION is the best possible way to redefine the
table without downtime. However, if you only pushed the table to
production a week or so ago, you may not have sufficient volume to
require a significant outage. Alternatively, if you can take the
downtime, you could use impdp/expdp or possibly just a straight INSERT
/*+ APPEND */.
On Thu, Mar 8, 2012 at 12:37 PM, Anupam Pandey <my.oralce_at_gmail.com> wrote:
> I have composite partitoned table ( range ,hash). When we were
> designing the table we thought that the number of rows for one particular
> range partition will be pretty huge and the kind
> of queries which are going to hit that table will always include the column
> (col2). So we decided to hash each partition on column col2 . Now after we
> pushed the table to production and got the volume metrics for a week ,it
> looks like that number of rows are not that huge ..and we dont necesarily
> need to has each partition ...for the kind of volume we have range
> partition is sufficient ..
> Now I am left with two questions
> 1.If I leave the table as it is then whats the harm with having
> unnecessary subpartitions ..
> 2. If i choose to rearrange the table to exclude the supartition is
> there a way other than dbms_redefinition .
-- Adam Musch ahmusch_at_gmail.com -- http://www.freelists.org/webpage/oracle-lReceived on Sun Mar 11 2012 - 22:03:53 CDT