Re: Are hash sub-partitions balanced?

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 7 May 2013 13:55:22 -0400
Message-ID: <CAE-dsOLWQPA4OXYUTi_K7+=812PeXmPbtnUyZxCOKP6ksZ6S3A_at_mail.gmail.com>

There are 800-1000 unique values and these are subject to change. Plus new ones can come in. The choice to hash sub-partition on this value was because we basically have 1 query that hits this table and the where clause is
date field (primary partition)
hash partitioned field
a different date field.

The first date field is the top level partition because we have very strict purge requirements and have to remove data hourly. It is based on the first field. I was concerned about deletes. I actually don't have a production like system to test this in and I do not have 100% confirmation on data volumes. The data comes from a 3rd party source. Basically it has to go to production with some guess work on data.

We expect the hash sub-partition data to be highly skewed.

On Thu, May 2, 2013 at 11:31 AM, Bobak, Mark <Mark.Bobak_at_proquest.com>wrote:

> What is the nature of the column you're partitioning on? How many unique
> values does that column have?
>
> -Mark
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Dba DBA
> Sent: Thursday, May 02, 2013 11:24 AM
> To: ORACLE-L
> Subject: Re: Are hash sub-partitions balanced?
>
> Followup. So we loaded some skewed data and tihs is a rolliup from my
> sub-partitions spread across several partitions and I this does not appear
> to be very well balanced. Is this something I can expect? I am following
> the power of 2 with 16 hash sub-partitions. I am also going to get more
> data loaded.
> I just analyzed the table with granularity='ALL'
>
> select subpartition_position,sum(num_rows)
> from user_tab_subpartitions
> where table_name = '<my table>'
> group by subpartition_position
> order by 1
> /
>
>
> SUBPARTITION_POSITION SUM(NUM_ROWS)
> --------------------- --------------------
> 1 18,337
> 2 32,184
> 3 18,259
> 4 22,271
> 5 6,944
> 6 15,905
> 7 28,929
> 8 41,466
> 9 19,585
> 10 21,391
> 11 23,895
> 12 8,206
> 13 26,849
> 14 12,293
> 15 21,284
> 16 8,395
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 07 2013 - 19:55:22 CEST