表分割槽機制測試

oracle_db發表於2012-05-29
範圍分割槽表測試：
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章