ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表分区机制测试

表分区机制测试

原创 Linux操作系统 作者:oracle_db 时间:2012-05-29 13:21:37 0 删除 编辑
范围分区表测试:
SQL> drop table range_example;

Table dropped.

SQL> CREATE TABLE range_example
  2  ( range_key_column date ,
  3    data             varchar2(20)
  4  )
  5  PARTITION BY RANGE (range_key_column)
  6  ( PARTITION part_1 VALUES LESS THAN
  7         (to_date('01/01/2010','dd/mm/yyyy')),
  8    PARTITION part_2 VALUES LESS THAN
  9         (to_date('01/01/2011','dd/mm/yyyy'))
 10  )
 11  /

Table created.

SQL> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '15/12/2012 00:00:00',
  5             'dd/mm/yyyy hh24:mi:ss' ),
  6    'application data...' );
insert into range_example
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


SQL> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '15-dec-2009 00:00:00',
  5             'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

SQL> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '31-dec-2009 23:59:59',
  5             'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

SQL> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '01-jan-2010 00:00:00',
  5             'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

SQL> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '31-dec-2010 00:00:00',
  5             'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );

1 row created.

SQL> insert into range_example
  2  ( range_key_column, data )
  3  values
  4  ( to_date( '31-dec-2012 00:00:00',
  5             'dd-mon-yyyy hh24:mi:ss' ),
  6    'application data...' );
insert into range_example
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


SQL> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')
  2    from range_example partition (part_1);

TO_CHAR(RANGE_KEY_COLUMN,'DD-M
---------------------------------------------------------------------------
15-dec-2009 00:00:00
31-dec-2009 23:59:59

SQL> select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')
  2    from range_example partition (part_2);

TO_CHAR(RANGE_KEY_COLUMN,'DD-M
---------------------------------------------------------------------------
01-jan-2010 00:00:00
31-dec-2010 00:00:00

SQL> 

散列分区测试:
散列分区设计为能使数据很好地分布在多个不同设备(磁盘)上,或者只是将数据聚集到更可管理的块(chunk)上,为表选择的散列键应当是惟一的一个列或一组列,或者至少有足够多的相异值,以便行能在多个分区上很好地(均匀地)分布

SQL> CREATE TABLE hash_example
  2  ( hash_key_column   date,
  3    data              varchar2(20)
  4  )
  5  PARTITION BY HASH (hash_key_column)
  6  ( partition part_1 tablespace qq,
  7    partition part_2 tablespace qq2
  8  )
  9  /

Table created.

SQL> insert into hash_example
  2  ( hash_key_column, data )
  3  values
  4  ( to_date( '25-jun-2010' ),
  5    'application data...' );

1 row created.

SQL> insert into hash_example
  2  ( hash_key_column, data )
  3  values
  4  ( to_date( '27-feb-2010' ),
  5    'application data...' );

1 row created.

SQL> select 'p1', hash_key_column from hash_example partition(part_1) union all
  2  select 'p2', hash_key_column from hash_example partition(part_2);

'P1'                             HASH_KEY_
-------------------------------- ---------
p1                               27-FEB-10
p2                               25-JUN-10

SQL> 
如何决定散列分区的数量?2的幂次方

SQL> 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';
 27  
 28      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 ||
 29   30                    replace(
 31   32   33   34                    replace(l_template,
 35                          '$POS$', x.pos),
 36   37   38                          '$PNAME$', x.pname );
 39      end loop;
 40  
 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.

SQL> variable x refcursor
SQL> set autoprint on
SQL> exec hash_proc( 4, :x );
exec hash_proc( 5, :x );
exec hash_proc( 6, :x );
exec hash_proc( 7, :x );
exec hash_proc( 8, :x );

PL/SQL procedure successfully completed.


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p1         ##########
*****************************

p2         ##########
*****************************

p3         ##########
******************************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p4         ##########
*****************************


SQL> 
PL/SQL procedure successfully completed.


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p1         ##########
**************

p2         ##########
*****************************

p3         ##########
******************************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p4         ##########
*****************************

p5         ##########
**************


SQL> 
PL/SQL procedure successfully completed.


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p1         ##########
**************

p2         ##########
**************

p3         ##########
******************************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p4         ##########
*****************************

p5         ##########
**************

p6         ##########
**************


6 rows selected.

SQL> 
PL/SQL procedure successfully completed.


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p1         ##########
***************

p2         ##########
***************

p3         ##########
***************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p4         ##########
******************************

p5         ##########
***************

p6         ##########
***************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p7         ##########
***************


7 rows selected.

SQL> 
PL/SQL procedure successfully completed.


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p1         ##########
*****************************

p2         ##########
*****************************

p3         ##########
*****************************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p4         ##########
****************************

p5         ##########
*****************************

p6         ##########
****************************


PNAME             CNT
---------- ----------
HG
--------------------------------------------------------------------------------
p7         ##########
******************************

p8         ##########
*****************************


8 rows selected.

SQL> 
结论--只有当分区数为2的幂次的时候散列分区的数据才趋向分布均匀。


列表分区:根据离散的值列表来指定一行位于哪个分区

SQL> create table list_example
  2  ( state_cd   varchar2(2),
  3    data       varchar2(20)
  4  )
  5  partition by list(state_cd)
  6  ( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
  7    partition part_2 values ( 'CT', 'RI', 'NY' )
  8  )
  9  /

Table created.
当插入数据与指定值不匹配时
SQL> insert into list_example values ( 'VA', 'data' );
insert into list_example values ( 'VA', 'data' )
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
如何增加默认分区
SQL> alter table list_example
  2  add partition
  3  part_3 values ( DEFAULT );

Table altered.

一旦列表分区表有一个DEFAULT 分区,就不能再向这个表中增加更多的分区了:
SQL> alter table list_example
  2  add partition
  3  part_4 values( 'CA', 'NM' );
alter table list_example
            *
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists

组合分区--,组合分区是区间分区和散列分区的组合,或者是区间分区与列表分区的组合。在组合分区中,顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区
SQL> CREATE TABLE composite_example
  2  ( range_key_column   date,
  3    hash_key_column    int,
  4    data               varchar2(20)
  5  )
  6  PARTITION BY RANGE (range_key_column)
  7  subpartition by hash(hash_key_column) subpartitions 2
  8  (
  9  PARTITION part_1
 10       VALUES LESS THAN(to_date('01/01/2008','dd/mm/yyyy'))
 11       (subpartition part_1_sub_1,
 12        subpartition part_1_sub_2
 13       ),
 14  PARTITION part_2
 15      VALUES LESS THAN(to_date('01/01/2011','dd/mm/yyyy'))
 16      (subpartition part_2_sub_1,
 17       subpartition part_2_sub_2
 18      )
 19  )
 20  /

Table created.

SQL> Insert into composite_example
  2  ( range_key_column, hash_key_column, data )
  3  Values
  4  ( to_date( '23-feb-2007', 'dd-mon-yyyy' ),
  5    123,
  6   'application_data' );

1 row created.

SQL> Insert into composite_example
  2  ( range_key_column, hash_key_column, data )
  3  Values
  4  ( to_date( '27-feb-2010', 'dd-mon-yyyy' ),
  5    456,
  6   'application_data' );

1 row created.

SQL> select range_key_column,hash_key_column,'part_1_sub_1' from composite_example subpartition(part_1_sub_1) union all
  2  select range_key_column,hash_key_column,'part_1_sub_2' from composite_example subpartition(part_1_sub_2) union all
  3  select range_key_column,hash_key_column,'part_2_sub_1' from composite_example subpartition(part_2_sub_1) union all
  4  select range_key_column,hash_key_column,'part_2_sub_2' from composite_example subpartition(part_2_sub_2);

RANGE_KEY HASH_KEY_COLUMN 'PART_1_SUB_1'
--------- --------------- --------------------------------
23-FEB-07             123 part_1_sub_1
27-FEB-10             456 part_2_sub_2

SQL> CREATE TABLE composite_range_list_example
  2  ( range_key_column   date,
  3    code_key_column    int,
  4    data               varchar2(20)
  5  )
  6  PARTITION BY RANGE (range_key_column)
  7  subpartition by list(code_key_column)
  8  (
  9  PARTITION part_1
 10       VALUES LESS THAN(to_date('01/01/2008','dd/mm/yyyy'))
 11       (subpartition part_1_sub_1 values( 1, 3, 5, 7 ),
 12        subpartition part_1_sub_2 values( 2, 4, 6, 8 )
 13       ),
 14  PARTITION part_2
 15      VALUES LESS THAN(to_date('01/01/2011','dd/mm/yyyy'))
 16      (subpartition part_2_sub_1 values ( 1, 3 ),
 17       subpartition part_2_sub_2 values ( 5, 7 ),
 18       subpartition part_2_sub_3 values ( 2, 4, 6, 8 )
 19      )
 20  )
 21  /

Table created.

SQL> 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-731368/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 测试TOM=自治事务
下一篇: SQLPLUS乱码一例
请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    313808