[分区表] 测试散列分区

create or replace procedure hash_proc
(
    p_nhash in number,
    p_cursor out sys_refcursor
)
    authid current_user as
    l_text long;
    l_template long := 'select $POS$ oc,''p$POS$'' pname,count(*) cnt from t partition ($PNAME$) union all ';

begin
    begin
        execute immediate 'drop table t';
        exception when others
            then null; 
    end;
    execute immediate 'create table t (id) partition by hash(id) partitions ' || p_nhash || ' as select rownum from all_objects';
    for x in
    (
        select partition_name pname,partition_position pos
        from user_tab_partitions
        where table_name = 'T' 
        order by partition_position
    )
        loop
            l_text := l_text || replace(replace(l_template,'$POS$',x.pos),'$PNAME$',x.pname);
        end loop;
    open p_cursor for
        'select pname,cnt,substr(rpad(''*'',30*round(cnt/max(cnt)over(),2),''*''),1,30) hg
         from (' || substr(l_text,1,length(l_text)-10) || ')
         order by oc';
end;
/
----------------------------------------------------------------------------
column hg format a50
variable x refcursorgo@DEMO> set autoprint on
exec hash_proc(4,:x);

PNAM        CNT HG
---- ---------- --------------------------------------------------
p1         9493 *****************************
p2         9488 *****************************
p3         9793 ******************************
p4         9516 *****************************

4 rows selected.