Re: sub-partition drop

From: Adam Musch <ahmusch_at_gmail.com>
Date: Sun, 11 Mar 2012 22:03:53 -0500
Message-ID: <CAH4Zrrv6XJ0RoB899=s3COMH9kSQk=nuvKQXVt9VksWD86Hg7g_at_mail.gmail.com>

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:
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Mar 11 2012 - 22:03:53 CDT