Oracle ora_hash() is a very useful function. I have used it for different purposes such as generating random number. The following query generate 5 buckets from 0 to 4, each of them have the similar number of records. First, we create a table and populate it with 1,000 numbers.
create table t_n (n number); begin for i in 1..1000 loop insert into t_n values(i); end loop; commit; end;In the query below, the parameter 5 of ora_hash defines the number of buckets is 5. As we see, each bucket has simlilar number of records.
with tbl as ( select ora_hash(n, 5) bucket, n from t_n) select bucket, count(*), min(n), max(n) from tbl group by bucket order by 1; BBUCKET COUNT(*) MIN(N) MAX(N) 0 154 2 993 1 164 7 999 2 168 6 991 3 175 4 995 4 173 8 1000
No comments:
Post a Comment