I use hash partitioning for a few of my very large tables, and occasionally I have a use case where it would be convenient to have a mechanism that would return the partition name that a row would be inserted into, given a partition value.
This blog here shows that we can use
ORA_HASH function for this purpose. Incidentally, it appears this page is the only page on the entire internet that explains this.
I’ve used it successfully and it works in all cases that I have tried. It seems
ORA_HASH is definitely what Oracle itself uses to pick the hash partition that it inserts data into, and that at least on the current version of Oracle it is safe to use for this use case.
However there is no guarantee in the documentation that Oracle even uses it, or will continue to use it in the future. This makes me think that using ORA_HASH in this way is not safe or future proof. What if a DB is upgraded and ORA_HASH no longer behaves this way?
For reference, you can use the following SQL to return the hash partition for a given value:
SELECT partition_name FROM all_tab_partitions WHERE table_name = 'FOO' AND partition_position = ORA_HASH('bar', n - 1) + 1
'bar' is the value you wish to analyze, and
n is the number of partitions in your table. There are some edge cases when the number of partitions is not a power of 2, which is covered in the blog article linked above.