Re: Are hash sub-partitions balanced?
Date: Thu, 2 May 2013 10:33:13 -0500
Message-ID: <CAH4Zrrts5TQRB+M9oUDJefvxtv8A75aQ16FmwbpWC+ysKPkz2A_at_mail.gmail.com>
Oracle assigns rows to subpartitions using the ora_hash() function; ora_hash(column_value, 0, hash_partition_count) If the data isn't functionally random or sequential - skew in the data is very likely to be seen in the subpartitions.
See Jonathan Lewis' note on this:
http://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/
On Thu, May 2, 2013 at 10:23 AM, Dba DBA <oracledbaquestions_at_gmail.com>wrote:
> 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
>
>
>
-- Adam Musch ahmusch_at_gmail.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 02 2013 - 17:33:13 CEST