ora_hash function

A quick demonstration script in response to a comment on a previous posting to demonstrate that ora_hash() works for numeric as well as character inputs.

First the script:

rem
rem	October 2009
rem	10.2.0.3
rem
rem	ora_hash() used to generate the partition
rem	id when the partitioning column is numeric
rem

spool hash_pt_demo

drop table t1;

create table t1 (
	id,
	v1
)
partition by hash(id)
partitions 6
as
select
	rownum,
	object_name
from
	all_objects
where
	rownum <= 8192
;

column partition_name new_value m_pt

--------------------------
--	Starting rowcounts
--------------------------

execute dbms_stats.gather_table_stats(user,'t1')

select	partition_position, partition_name, num_rows
from	user_tab_partitions
where	table_name = 'T1'
order by
	partition_position
;

-------------------------
--	Split a partition
-------------------------

alter table t1 add partition;

execute dbms_stats.gather_table_stats(user,'t1')

select	partition_position, partition_name, num_rows
from	user_tab_partitions
where	table_name = 'T1'
order by
	partition_position
;

-------------------------------
--	Coalesce two partitions
-------------------------------

alter table t1 coalesce partition;

execute dbms_stats.gather_table_stats(user,'t1')

select	partition_position, partition_name, num_rows
from	user_tab_partitions
where	table_name = 'T1'
order by
	partition_position
;

-------------------------------------
--
--	using ora_hash(value, base)
--	Eight partitions in table, so
--	set base to 7; check partition
--	six (external) should all give
--	value five. But partition three
--	(external) should give values
--	two and six (for 3 and 7).
--
--	The pmarker is the object_id
--
--------------------------------------

prompt	===============================
prompt	ora_hash and dbms_mview.pmarker
prompt	on partition 6 (of 6)
prompt	===============================

select	partition_name
from	user_tab_partitions
where	table_name = 'T1'
and	partition_position = 6
;

select
	ora_hash(id, 7),
	dbms_mview.pmarker(rowid),
	count(*)
from
	t1 partition( &m_pt )
group by
	ora_hash(id, 7),
	dbms_mview.pmarker(rowid)
;

prompt	===============================
prompt	ora_hash and dbms_mview.pmarker
prompt	on partition 3 (of 6)
prompt	===============================

select	partition_name
from	user_tab_partitions
where	table_name = 'T1'
and	partition_position = 3
;

select
	ora_hash(id, 7),
	dbms_mview.pmarker(rowid),
	count(*)
from
	t1 partition( &m_pt )
group by
	ora_hash(id, 7),
	dbms_mview.pmarker(rowid)
;

spool off

Then a sample of output from 10.2.0.3:

Table dropped.

Table created.

PL/SQL procedure successfully completed.

PARTITION_POSITION PARTITION_NAME         NUM_ROWS
------------------ -------------------- ----------
                 1 SYS_P56                    1037
                 2 SYS_P57                    1005
                 3 SYS_P58                    2086
                 4 SYS_P59                    2042
                 5 SYS_P60                     961
                 6 SYS_P61                    1061

6 rows selected.

Table altered.

PL/SQL procedure successfully completed.

PARTITION_POSITION PARTITION_NAME         NUM_ROWS
------------------ -------------------- ----------
                 1 SYS_P56                    1037
                 2 SYS_P57                    1005
                 3 SYS_P58                    1053
                 4 SYS_P59                    2042
                 5 SYS_P60                     961
                 6 SYS_P61                    1061
                 7 SYS_P62                    1033

7 rows selected.

Table altered.

PL/SQL procedure successfully completed.

PARTITION_POSITION PARTITION_NAME         NUM_ROWS
------------------ -------------------- ----------
                 1 SYS_P56                    1037
                 2 SYS_P57                    1005
                 3 SYS_P58                    2086
                 4 SYS_P59                    2042
                 5 SYS_P60                     961
                 6 SYS_P61                    1061

6 rows selected.

===============================
ora_hash and dbms_mview.pmarker
on partition 6 (of 6)
===============================

PARTITION_NAME
--------------------
SYS_P61

1 row selected.

ORA_HASH(ID,7) DBMS_MVIEW.PMARKER(ROWID)   COUNT(*)
-------------- ------------------------- ----------
             5                     48272       1061

1 row selected.

===============================
ora_hash and dbms_mview.pmarker
on partition 3 (of 6)
===============================

PARTITION_NAME
--------------------
SYS_P58

1 row selected.

ORA_HASH(ID,7) DBMS_MVIEW.PMARKER(ROWID)   COUNT(*)
-------------- ------------------------- ----------
             2                     48275       1053
             6                     48275       1033

2 rows selected.