Re: Understanding partitioning by hash

With Oracle 8i you have RANGE partioning, HASH partitioning or a combination of the two (first RANGE and then HASH in subpartitions).

In the first case, RANGE partitioning, you specify for each partition a LESS THAN clause, which means that all rows with a partitioning key between the LESS THAN clause of the previous partition and the LESS THAN of this partition will go in this partition. The last partition has either a LESS THAN clause with a fixed value or a MAXVALUE (infinity) value. In the latter case, this last partition is sometimes called the overflow partition. Unless you have a partition with the MAXVALUE, you risk to insert rows into a 'non-existant' partition. The partition key may be beyond the LESS THAN value of the last partition. In that case, you will receive an ORA- error. In fact, there is an order of the partitions.

With HASH partitioning, you specify at creation time of the table, the number of partitions you want. Each rows will go in one of this partitions, the one determined by the hash value returned by Oracle's internal hash function. ALL inserts, whatever the value of the partition key is, will ALWAYS go in one of these partitions. An insert will NEVER fail on a 'partition non existant' error.

How Oracle makes sure that, whatever the distribution of the partitioning key of all the rows is, you will end up with an almost evenly distribution over the partitions, is Oracle's secret of this internal hash function. It is more complicated than the simple MODULO function I used as an example in my privious post.

Now, what if you update the partitioning key? If you are lucky, the hash value of the new partitioning key is the same as the hash value of the old partitioning key, and the row will stay in the same partition. If NOT, the row will be moved to another partition if and only if you specified ENABLEd ROW MOVEMENT in the CREATE statement of the table, or if you forgot, you can do an ALTER TABLE <table> ENABLE ROW MOVEMENT. If no ROW MOVEMENT is enabled, you will receive an ORA- error, in the case the row should be moved.

If you take a look at the DBMS_UTILITY function:   function get_hash_value(name varchar2, base number, hash_size number) name is the partitioning key,
base is the low end range, for example 0 hash_size is the number of partitions, for example 16.

For the example values, this function will return a number between 0 and 15 for whatever value you give for the 'name' argument.

Try this:

select dbms_utility.get_hash_value(ename,0,16),count(*) from emp
group by dbms_utility.get_hash_value(ename,0,16) /

(or a bigger table, to better see the effect)

You will see that you will get the values 0 to 15, each with more or less the same count(*)

Good luck.

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:8hu8ovckc7gnhk223pf6csfvpg46eqvfqe_at_4ax.com...
> "Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote:
>
>
> > Oracle will make
> >sure there will be an (almost) evenly distribution. In our database the
> >difference between the less and most populated partitions is only about
10%.
> >
> >An example of a hash function (for VARCHAR2) is in DBMS_UTILITY:
> >
> > function get_hash_value(name varchar2, base number, hash_size number)
> >
> >These functions are deterministic, they will always return the same value
> >for the same key!
> >So all rows with the same partitioning key will go into the same
partition!
>
> If the partition for a record is deterministically dependent on the
> partition key, how can oracle distribute them (almost) evenly? This
> point remains still unclear for me.
>
> Partitioning is transparent for the SQL code, so it does not need to
> take partitioning into consideration when doing a select statement.
> But what if doing inserts? Will Oracle automatically create the
> corresponding partitions when new values of the partitioning key are
> used? I hope so, otherwise any insert statement that would put a
> record on an non-existing partition (because the partitioning key is
> new) will fail!
>
> I am trying hard to learn enough about partitioning, but I am afraid
> that the "gotchas" will appear when the partitioned table is already
> in production... I need to be sure that after partitioning a table,
> everything will still work like before, with no adverse consequences
> and no need of special administrative care (of course, rebuilding
> indexes of local partitions will occur partitionwise, but that shuld
> not be a problem).
>
> Thanks for your explanation!
>
> Rick Denoire
Received on Wed Oct 08 2003 - 16:57:01 CDT