Hash index stores key value pairs based on a pseudo randomizing function called hash function , hashing compacts the input based on hasing function such as more set of values could have similar hash value
Hashin index generally found on hash partitioned table as global partition hash indexes reduces high contention of the leaf blocks
create index hg_idx on tab (c1,c2,c3)
global partition by hash (c1,c2)
(partition p1 tablespace tbs_1,
partition p2 tablespace tbs_2,
partition p3 tablespace tbs_3,
partition p4 tablespace tbs_4);
what is hash index,in which case we have to use hash index in oracle . And please give me one simple example .
You never HAVE to use any a hash, or any other, index. You only use an index if it provides some benefit.
A hash index is often used when there is a lot of data that you want to break into separate partitions or subpartitions or when you have a set of distinct values that you want to partition on and don't want to have to specify all of the partitions individually.
An example would be if you have data for the 50 United States and you want to partition by state. You could create a hash-partitioned table on STATE_NAME or STATE_CODE (50 distinct values) and you would likely get 50 partitions. That would be easier that creating a list-partitioned table and having to specify 50 different 'lists' (one for each state).
Oracle would still do partition pruning if your queries used filter predicates such as: WHERE STATE_NAME = 'CALIFORNIA'. Only one partition would be used in the query even though you used hash-partitioning. That is because the word 'CALIFORNIA' always hashes to the same value and Oracle can do that hash when the query is compiled and determine that only one partition is needed.