Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

oracle - Is ora_hash deterministic?

I am working with an Oracle database and I need to be able to partition the data in a table. I understand that Rracle has an ora_hash function that can partition the data into buckets. Is the ora_hash function deterministic?

In my program I will be making several different database queries with each query asking for a different bucket number.

For example, in one query I might ask for the first two buckets:

SELECT * FROM sales WHERE ORA_HASH(cust_id, 9) in (0,1);

In a subsequent query I might ask for the 2nd and 3rd bucket:

SELECT * FROM sales WHERE ORA_HASH(cust_id, 9) in (1,2);

In the above example, will ora_hash always divide the table into the exact same 10 buckets? Assume that the data in the tables hasn't changed. Will the second bucket (bucket 1), be identical in both queries?

There is documentation that suggests that seed value enables oracle to return different results for the same data set. So I am assuming that if I don't use seed value, then ora_hash will be deterministic. See the documentation.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

ORA_HASH is definitely deterministic for data types that can be used for partitioning, such as NUMBER, VARCHAR, DATE, etc.

But ORA_HASH is not deterministic for at least some of the other data types, such as CLOB.


My answer is based on this Jonathan Lewis article about ORA_HASH.

Jonathan Lewis doesn't explicitly say they are deterministic, but he does mention that ORA_HASH "seems to be the function used internally – with a zero seed – to determine which partition a row belongs to in a hash partitioned table". And if it's used for hash partitioning then it must be deterministic, or else partition-wise joins wouldn't work.

To show that ORA_HASH can be non-deterministic for some data types, run the below query. It's from a comment in the same article:

with src as (select to_clob('42') val from dual connect by level<=5)
select val,ora_hash(val,7) from src order by 2;

Surprisingly, this same issues happens with dbms_sqlhash.gethash.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...