以下是关于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
一、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/