Re: sub-partition drop
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: my.oralce@xxxxxxxxx
- 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@xxxxxxxxx> wrote:
> Hi,
> 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 .
>
> Thanks,
> Anupam
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
