Database Reference
In-Depth Information
Now, we'll take that query and select out the partition position (
PNAME
) and the count of rows in that partition
(
CNT
). Using
RPAD
, we'll construct a rather rudimentary but effective histogram:
41 open p_cursor for
42 'select pname, cnt,
43 substr( rpad(''*'',30*round( cnt/max(cnt)over(),2),''*''),1,30) hg
44 from (' || substr( l_text, 1, length(l_text)-11 ) || ')
45 order by oc';
46
47 end;
48 /
Procedure created.
If we run this with an input of 4, for four hash partitions, we would expect to see output similar to the following:
EODA@ORA12CR1> variable x refcursor
EODA@ORA12CR1> set autoprint on
EODA@ORA12CR1> exec hash_proc( 4, :x );
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- ------------------------------
p1 12141 *****************************
p2 12178 *****************************
p3 12417 ******************************
p4 12105 *****************************
The simple histogram depicted shows a nice, even distribution of data over each of the four partitions. Each
has close to the same number of rows in it. However, if we simply go from four to five hash partitions, we'll see the
following:
EODA@ORA12CR1> exec hash_proc( 5, :x );
PL/SQL procedure successfully completed.
PN CNT HG
-- ---------- ------------------------------
p1 6102 **************
p2 12180 *****************************
p3 12419 ******************************
p4 12106 *****************************
p5 6040 **************
