ora_hash() 与 get_hash_value 区别

来源：互联网发布：centos cuda8安装编辑：程序博客网时间：2024/06/02 07:02

一、ora_hash格式：

ora_hash('strings', N, 0 )
    strings: 输入值
    N：最大hash bucket的值
    0：起始hash bucket值，缺省是1

dbms_utility.get_hash_value(‘strings', min, max)
    strings：输入值
    min：hash bucket最小值
    max：hash bucket最大值

二、 ora_hash() 用于计算hash分区表数据的分布位置，实验如下：

1、创建hash 分区表：

delete from wf_order where id=:1  and version=:2     create table t1 (                id,                v1                    )         partition by hash(v1)         partitions 8         as              select                   rownum,                   object_name              from                   all_objects              where                  rownum <= 32768;

2 、收集统计信息：

execute dbms_stats.gather_table_stats('SYS','T1');

3、检查每个分区的数据量，两种方法的结果一样

11:06:28 sys@READDB3>select    partition_position, partition_name, num_rows11:06:34   2             from    user_tab_partitions11:06:34   3              where    table_name = 'T1'11:06:34   4              order by11:06:34   5              partition_position;PARTITION_POSITION PARTITION_NAME                   NUM_ROWS------------------ ------------------------------ ----------                 1 SYS_P33367                           3627                 2 SYS_P33368                           4022                 3 SYS_P33369                           3643                 4 SYS_P33370                           4218                 5 SYS_P33371                           3700                 6 SYS_P33372                           5795                 7 SYS_P33373                           4204                 8 SYS_P33374                           35598 rows selected.

11:06:35 sys@READDB3>  select11:06:45   2                     ora_hash(v1, 7)+1                ora_hash,11:06:45   3                     count(*)11:06:45   4                from   t1 11:06:45   5                group by11:06:45   6                ora_hash(v1, 7)11:06:45   7                order by 1;  ORA_HASH   COUNT(*)---------- ----------         1       3627         2       4022         3       3643         4       4218         5       3700         6       5795         7       4204         8       35598 rows selected.

0 0