Purpose
ORA_HASH() calculates the hash value of an expression.
Syntax
ORA_HASH(expr [, max_bucket [, seed_value ]])
Parameters
| Parameter | Description |
|---|---|
| expr | The expression whose hash value is to be calculated. Usually, the expression is the name of a column in a database table. The data type can be numeric, string, datetime, or RAW. |
| max_bucket | The maximum bucket value returned by the hash function. This parameter is optional. Value range: [0, 4294967295]. Default value: 4294967295. |
| seed_value | Enables the database to generate different results for the same group of data. This parameter is optional. Value range: [0, 4294967295]. Default value: 0. |
Return type
The return type is NUMBER.
Examples
Create a table named tbl1 and insert data into it. Use the ORA_HASH function to query the hash value of the col1 column in the tbl1 table, divide the hash value into a maximum of 10 buckets, and set the third parameter to 5.
obclient> CREATE TABLE tbl1(col1 CHAR(6),col2 NUMBER(10,2));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES('ABC', 1000),('DEF', 1100),('GHI', 1200),('JKL', 1300),
('MNO', 1400),('PQR', 1500),('STU', 1600),('VWX', 1700),('YZ1', 1800),('234', 1900);
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0
obclient> SELECT ORA_HASH(col1,10,5),col1,col2 FROM tbl1;
+---------------------+--------+------+
| ORA_HASH(COL1,10,5) | COL1 | COL2 |
+---------------------+--------+------+
| 5 | ABC | 1000 |
| 3 | DEF | 1100 |
| 8 | GHI | 1200 |
| 3 | JKL | 1300 |
| 3 | MNO | 1400 |
| 1 | PQR | 1500 |
| 4 | STU | 1600 |
| 2 | VWX | 1700 |
| 10 | YZ1 | 1800 |
| 5 | 234 | 1900 |
+---------------------+--------+------+
10 rows in set