表分区机制测试

范围分区表测试：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15720542/viewspace-731368/