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/，如需轉載，請註明出處，否則將追究法律責任。