ora_hash( ) 笔记

       以下是关于jonathan lewis博客中关于ora_hash介绍的笔记

一、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最大值

二、两者比较

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_value

3. 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                                     4359

4、用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                        3974
T1                  SYS_P28                        4064
T1                  SYS_P29                        4123
T1                  SYS_P30                        3991
T1                  SYS_P31                        4055
T1                  SYS_P32                        4072
T1                  SYS_P33                        4130
T1                   SYS_P34                       4359

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/354732/viewspace-622455/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-622455/