Database Reference
In-Depth Information
Hash Partition Using Powers of Two
I mentioned earlier that the number of partitions should be a power of two. This is easily observed to be true.
To demonstrate, we'll set up a stored procedure to automate the creation of a hash partitioned table with N partitions
(N will be a parameter). This procedure will construct a dynamic query to retrieve the counts of rows by partition and
then display the counts and a simple histogram of the counts by partition. Lastly, it will open this query and let us see
the results. This procedure starts with the hash table creation. We will use a table named
T
:
EODA@ORA12CR1> create or replace
2 procedure hash_proc
3 ( p_nhash in number,
4 p_cursor out sys_refcursor )
5 authid current_user
6 as
7 l_text long;
8 l_template long :=
9 'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' ||
10 'from t partition ( $PNAME$ ) union all ';
11 table_or_view_does_not_exist exception;
12 pragma exception_init( table_or_view_does_not_exist, -942 );
13 begin
14 begin
15 execute immediate 'drop table t';
16 exception when table_or_view_does_not_exist
17 then null;
18 end;
19
20 execute immediate '
21 CREATE TABLE t ( id )
22 partition by hash(id)
23 partitions ' || p_nhash || '
24 as
25 select rownum
26 from all_objects';
Next, we will dynamically construct a query to retrieve the count of rows by partition. It does this using the
template query defined earlier. For each partition, we'll gather the count using the partition-extended table name and
union all of the counts together:
28 for x in ( select partition_name pname,
29 PARTITION_POSITION pos
30 from user_tab_partitions
31 where table_name = 'T'
32 order by partition_position )
33 loop
34 l_text := l_text ||
35 replace(
36 replace(l_template,
37 '$POS$', x.pos),
38 '$PNAME$', x.pname );
39 end loop;
