ora_hash

一、ora_hash格式：ora_hash('strings', N, 0 )    strings: 输入值    N：最大hash bucket的值    0：起始hash bucket值，缺省是1dbms_utility.get_hash_value(‘strings', min, max)    strings：输入值    min：hash bucket最小值    max：hash bucket最大值二、两者比较1. 两者计算结果是相同，如：SQL> select   dbms_utility.get_hash_value('strings', 1, 1024),                          ora_hash('strings', 1024,1)           from dual;2. ora_hash的运行效率高于dbms_utility.get_hash_value3. ora_hash在10g版本才进入三、ora_hash用于计算hash分区表数据的分布位置，实验如下：1、创建hash分区表      create table t1 (                id,                v1                    )         partition by hash(v1)         partitions 8         as              select                   rownum,                   object_name              from                   all_objects              where                  rownum <= 32768;2、统计分析SQL>execute dbms_stats.gather_table_stats(user,'t1')3、检查每个分区数据分布数量SQL>select    partition_position, partition_name, num_rows           from    user_tab_partitions            where    table_name = 'T1'            order by            partition_position;PARTITION_POSITION PARTITION_NAME                   NUM_ROWS--------------------------------- ----------------------------------------- ----------                 1                         SYS_P27                                     3974                 2                         SYS_P28                                     4064                 3                         SYS_P29                                     4123                 4                         SYS_P30                                     3991                 5                         SYS_P31                                     4055                 6                         SYS_P32                                     4072                 7                         SYS_P33                                     4130                 8                         SYS_P34                                     43594、用ora_hash计算分区分布，进行比较SQL>  select                   ora_hash(v1, 7)+1                ora_hash,                   count(*)              from   t1              group by              ora_hash(v1, 7)              order by 1;       ORA_HASH               COUNT(*)        ---------------------- ----------                   1                         3974                   2                         4064                   3                         4123                   4                         3991                   5                        4055                   6                        4072                   7                        4130                   8                        4359其结果完全一致。题外：通过dbms_mview.pmaker(rowid)返回 data_object_id计算各个分区的数量SQL>select               obj.object_name,               obj.subobject_name,               rct.row_count           from               (                select                     /*+ no_merge */                    object_name,                    subobject_name,                    data_object_id                  from                       user_objects                  where                       object_type like 'TABLE%'                  and    subobject_name is not null                  )    obj,               (                    select                        /*+ no_merge */                        dbms_mview.pmarker(rowid)    data_object_id,                        count(*)             row_count                    from                       t1                   group by                       dbms_mview.pmarker(rowid)                  )    rct           where               rct.data_object_id = obj.data_object_id          order by              obj.object_name,             obj.subobject_name              ;OBJEC SUBOBJECT_NAME        ROW_COUNT------------- ------------------------------- -------------------T1                  SYS_P27                        3974T1                  SYS_P28                        4064T1                  SYS_P29                        4123T1                  SYS_P30                        3991T1                  SYS_P31                        4055T1                  SYS_P32                        4072T1                  SYS_P33                        4130T1                   SYS_P34                       4359