ora_hash function

A few weeks ago I wrote a note demonstrating the way in which Oracle’s strategy for hash partitioning is engineered to give an even data distribution when the number of partitions is a power of two. In one of the comments, Christo Kutrovsky pre-empted my planned follow-up by mentioning the hashing function ora_hash() that appeared in 10g.

This function takes up to three parameters: the input value (which can be of any base type), the maximum hash bucket value (the minimum value is implicitly zero) and a seed value (default zero) for the hash function. So, for example, ora_hash(‘fred’,1023) will return an integer value between zero and 1023.

The ora_hash() function is similar to the packaged function dbms_utility.get_hash_value({string},{low value},{hash size}) that appeared in 8i, although the older function only handles character expressions (using an implicit conversion for dates and numbers), requires you to specify the minimum value and hash table size, and doesn’t allow you to introduce a seed value. [Corrected thanks to note 10 below].

In fact, for character inputs, the ora_hash() and get_hash_value() functions can be mapped to each other very easily by the relationship:

dbms_utility.get_hash_value(string, 1, N) = 1 + ora_hash(string, N – 1, 0)

So, for example, we can run a little query like:

select
	1 + ora_hash('fred',1023,0)			ora_hash_plus_one,
	dbms_utility.get_hash_value('fred',1,1024)	get_hash_value
from
	dual
/

ORA_HASH_PLUS_ONE GET_HASH_VALUE
----------------- --------------
              904            904

One important feature of the ora_hash() function is that it seems to be the function used internally – with a zero seed – to determine which partition a row belongs to in a hash partitioned table. Of course, as I pointed out in the previous article, you have to tailor the use of the function to the number of partitions you have declared – rounding up to the nearest power of two (and subtracting one) for the “max bucket” input, then adding one to the result, then dropping the highest bit of the result if the number of partitions is not a power of two.

Here’s a little demonstration, based on a table with six partitions (i.e. poor choice of partition count), running under 11.1.0.6:

create table t1 (
	id,
	v1
)
partition by hash(v1)
partitions 6
as
select
	rownum,
	object_name
from
	all_objects
where
	rownum <= 32768
;

execute dbms_stats.gather_table_stats(user,'t1')

select	partition_position, partition_name, num_rows
from	user_tab_partitions
where	table_name = 'T1'
order by
	partition_position
;

column partition_name new_value m_pt

select	partition_name
from	user_tab_partitions
where	table_name = 'T1'
and	partition_position = 3
;

select
	ora_hash(v1, 7)				ora_hash,
	dbms_utility.get_hash_value(v1,1,8)	get_hash_value,
	dbms_mview.pmarker(rowid)		pmarker,
	count(*)
from
	t1 partition( &m_pt )
group by
	ora_hash(v1, 7),
	dbms_utility.get_hash_value(v1,1,8),
	dbms_mview.pmarker(rowid)
;

You’ll see that I’ve collected stats on the table then reported each partition’s position, name, and number of rows . This is the result I got from that query:

PARTITION_POSITION PARTITION_NAME         NUM_ROWS
------------------ -------------------- ----------
                 1 SYS_P608                   3989
                 2 SYS_P609                   4055
                 3 SYS_P610                   8356
                 4 SYS_P611                   8320
                 5 SYS_P612                   4083
                 6 SYS_P613                   3965

As you might expect from the previous article, two of my partitions (numbers 3 and 4) are twice the size of the rest. If I were to issue a ‘split partition’ call then partition 3 would split (roughly 50/50) into partitions 3 and 7, and on a second ‘split partition’ call partition 4 would split (again 50/50) into partitions 4 and 8.

To demonstrate the effect of the ora_hash() function and how it relates to hash partitioning I’ve then run a query that references partition 3 by name (using an earlier query to convert a partition position into a partition name).

You’ll notice that my call to ora_hash() uses the value seven for the maximum hash bucket – this is because (a) my ideal partition count is eight (the smallest power of two that is not less than six), and (b) although I want values between 1 and 8 the function will be generating values between zero and 7. Given the relationship between ora_hash() and dbms_utility.get_hash_value() – and given that I’m applying the functions to a character string – I’ve also called the get_hash_value() function to show that its behaviour really is consistent with ora_hash() across the entire data set. Here are the results from the final query:

  ORA_HASH GET_HASH_VALUE    PMARKER   COUNT(*)
---------- -------------- ---------- ----------
         2              3      86154       4194
         6              7      86154       4162

You can see that both functions have split my set of values into two distinct sets in exactly the same way – values that would go into a new partition three on a “split partition”, and values that would go into partition seven. And the split is roughly 50/50 (to within 1%).

As a little bonus, I’ve also included a call to dbms_mview.pmarker() in the query. This was a function that Oracle introduced in 9i (I think) to allow more refresh options for materialized views on partitioned objects when you used partition maintenance operations on them. The function returns an identifier for the partition of a partitioned table that the rowed belongs to. (In fact all it is effectively doing is returning the data_object_id from dba_objects for the containing partition).

Once you’re aware of how ora_hash() works you may be able to find ways to take advantage of it. Occasionally I’ve been asked if it’s possible to work out which partition some data is going to go into – now you know that the answer is yes.

To be investigated: Does the ora_hash() function play any part in the distribution of data across hash clusters ? (Probably not – the manuals make some comments about rounding up the supplied number of hashkeys to the next larger  prime number, which suggests that a different hashing algorithm is used.

To be confirmed: Does the ora_hash() function plan any part in parallel query distribution by hash ? One quick test suggests not – but the method of use in this case may be more subtle than the “obvious” guess that I used in my test.