Question:
What is the ora_hash function
and how do I use it?
Answer:
ora_hash is a function that computes a hash value for a
given expression. This function has 3 parameters: expr;
max_bucket; and seed_value. The expr
parameter determines the data that you want Oracle to compute a hash
for, and it can be any type or length of data. max_bucket
and seed_value are both optional. The first can be any
number between 0 and 4294967295 and defaults to the latter.
seed_value enables Oracle to produce different results for the
same set of data, and while it has the same upper and lower bounds
as max_bucket, the default for seed_value is 0.
The ora_hash function returns a NUMBER value.
The following example creates a hash for each combination of
customer ID and product ID from the sh.sales tables, divides the
hash values into 100 different buckets, and then returns the sum of
the amount_sold values in the first bucket. By adding or
changing a seed_value, you can get different hash results
from the same query.
SELECT SUM(amount_sold) FROM sales
WHERE ORA_HASH(CONCAT(cust_id, prod_id), 99) = 0;
SUM(AMOUNT_SOLD)
----------------
7315
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
