Re: Understanding partitioning by hash

With hash partioning, Oracle uses a built in function (from Oracle 9 one, you can define one yourself) to calculate a number between 1 and n = the number of partitions you asked for. It is something like the MOD function for NUMBERS:

MOD(key,n) gives a number between 0 and n-1.

The internal function used by Oracle is more complicated than that. If you use 4 partitions and all (NUMBER) keys have a MODULO(4) equals to 3, all rows would go into partition 3, leaving the others empty. 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 you have the key used in the where clause, Oracle uses the hash function to calculate the partition number and that only partition is accessed.

The PLAN_TABLE has has three columns that will give you the info which partitions are accessed:

PARTITION_START
PARTITION_STOP
PARTITION_ID

If you need an index on this key, partition it the same way you partitioned the table (LOCAL partitioned index).

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:fsd6ovkdev5eh062m9u2v9mhuc83pqos2c_at_4ax.com...
> I would like to partition a big table (Oracle 8.1.7) using a key which
> is not suitable for range partitioning. If I use hash partitioning
> instead, will still partition pruning take place? Will all records
> having the same key value go to the same partition? (If not, I am
> afraid that partition pruning won't occur when the key is used in the
> where clause, meaning that the corresponding records would be spread
> accross several partitions).
>
> Thanks a lot
> Rick Denoire
Received on Tue Oct 07 2003 - 17:41:20 CDT