Re: Understanding partitioning by hash

"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:07:42 CDT