sql - How to achieve bucketing concept using ora-hash function

Question

I've researched on the Internet but couldn't find an answer.

How to achieve the bucketing concept using the ora-hash function could you please guide me?

Answer 1

"Using ORA_HASH function" only approximates bucketing; you can decide the number of buckets, and use ORA_HASH to assign entities to buckets, but there is no guarantee that the assignment will be balanced. It should be "more or less" balanced (approximately equal numbers of entities assigned to each bucket), but it won't be exact. So, first question: Why does it have to be ORA_HASH and not some other method?

With that said, here is one way to do what you asked. (Noting again that "what you asked" doesn't make very much sense in the first place.) Suppose you have the 14 employees in the standard SCOTT.EMP table and you must assign them (randomly) to three buckets. ORA_HASH creates buckets numbered from 0 to N (where you give N as an input to the function); if you want the buckets to be 1, 2, 3, then you should run ORA_HASH with N = 2 (to create buckets 0, 1, 2) and then add 1 to the result so the buckets are numbered 1, 2, 3.

Other than that, you can apply ORA_HASH to many data types. One dumb idea is to, then, apply ORA_HASH to the employee's last name (for example). It's dumb because last names generally aren't distinct; there are many Smith and Jones (in the U.S.) and very few Pryxz, so whichever bucket has Smith will likely have many more employees that other buckets (because ORA_HASH assigns a bucket to the name, not to the employee). You must apply ORA_HASH to something unique about each employee; for example to the EMPNO, or - even simpler - to the Oracle-generated ROWNUM.

select 1 + ora_hash(rownum, 2) as bucket, ename
from   scott.emp
order  by bucket, ename
;

BUCKET  ENAME     
------  ----------
     1  ALLEN     
     1  FORD      
     1  JONES     
     1  MARTIN    
     1  MILLER    
     2  ADAMS     
     2  CLARK     
     2  JAMES     
     2  KING      
     2  SCOTT     
     3  BLAKE     
     3  SMITH     
     3  TURNER    
     3  WARD