interval間隔分區STORE IN參數的作用範圍

interval間隔分區STORE IN參數的作用範圍

Oracle 11g推出了interval間隔分區，以往的分區是需要手工或半自動化指令碼實現分區擴充，但這種間隔分區的出現，將分區擴充的工作徹底解放出來，這裡不討論何為間隔分區，主要說一下建立間隔分區有一個STORE IN參數，官方文旦對其的介紹是：

The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

STORE IN參數可以明確間隔分區使用的一個或多個資料表空間，他使用的是迴圈演算法來建立間隔分區。

接下來，分別有三種方法來指定間隔分區的資料表空間，我們看下各自的不同。

方法1：設定store in，未設定預定義分區資料表空間。

CREATE TABLE interval_sales1
    ( prod_id NUMBER(6)
    , cust_id NUMBER , time_id DATE , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
    )
  PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) store in (SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) ); insert into interval_sales1 values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200); insert into interval_sales1 values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800); insert into interval_sales1 values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700); insert into interval_sales1 values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600); insert into interval_sales1 values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500); commit;

方法2：未設定store in,

設定預定義分區資料表空間。

CREATE TABLE interval_sales2
    ( prod_id NUMBER(6)
    , cust_id NUMBER , time_id DATE , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
    )
  PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')) tablespace SALES_TBS1,
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')) tablespace SALES_TBS2,
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')) tablespace SALES_TBS3,
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) tablespace SALES_TBS4); insert into interval_sales2 values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200); insert into interval_sales2 values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800); insert into interval_sales2 values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700); insert into interval_sales2 values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600); insert into interval_sales2 values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500); commit;

方法3：設定store in, 設定預定義分區資料表空間。

CREATE TABLE interval_sales3
    ( prod_id NUMBER(6)
    , cust_id NUMBER , time_id DATE , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
    )
  PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR')) store in (SALES_TBS1, SALES_TBS2, SALES_TBS3, SALES_TBS4)
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2003', 'DD-MM-YYYY')) tablespace SALES_TBS1,
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2004', 'DD-MM-YYYY')) tablespace SALES_TBS2,
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')) tablespace SALES_TBS3,
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) tablespace SALES_TBS4); insert into interval_sales3 values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200); insert into interval_sales3 values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800); insert into interval_sales3 values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700); insert into interval_sales3 values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600); insert into interval_sales3 values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500); commit;

我們看見三種方法對STORE IN和預定義分區進行了窮舉，我們看看不同方法對於間隔分區資料表空間的使用有何區別。

select table_name, partition_name, tablespace_name, high_value
from user_tab_partitions where table_name like 'INTERVAL%';
TABLE_NAME        PARTITION_NAME  TABLESPACE_NAME  HIGH_VALUE
----------------- --------------- ---------------- --------------------------------------------------
INTERVAL_SALES1  P0              USERS            TO_DATE(' 2003-01-01 00:00:00'...)
INTERVAL_SALES1  P1              USERS            TO_DATE(' 2004-01-01 00:00:00'...)
INTERVAL_SALES1  P2              USERS            TO_DATE(' 2005-01-01 00:00:00'...)
INTERVAL_SALES1  P3              USERS            TO_DATE(' 2006-01-01 00:00:00'...)
INTERVAL_SALES1  SYS_P64        SALES_TBS2      TO_DATE(' 2008-01-01 00:00:00'...)
INTERVAL_SALES2  P0              SALES_TBS1      TO_DATE(' 2003-01-01 00:00:00'...)
INTERVAL_SALES2  P1              SALES_TBS2      TO_DATE(' 2004-01-01 00:00:00'...)
INTERVAL_SALES2  P2              SALES_TBS3      TO_DATE(' 2005-01-01 00:00:00'...)
INTERVAL_SALES2  P3              SALES_TBS4      TO_DATE(' 2006-01-01 00:00:00'...)
INTERVAL_SALES2  SYS_P65        USERS            TO_DATE(' 2008-01-01 00:00:00'...)
INTERVAL_SALES3  P0              SALES_TBS1      TO_DATE(' 2003-01-01 00:00:00'...)
INTERVAL_SALES3  P1              SALES_TBS2      TO_DATE(' 2004-01-01 00:00:00'...)
INTERVAL_SALES3  P2              SALES_TBS3      TO_DATE(' 2005-01-01 00:00:00'...)
INTERVAL_SALES3  P3              SALES_TBS4      TO_DATE(' 2006-01-01 00:00:00'...)
INTERVAL_SALES3  SYS_P66        SALES_TBS2      TO_DATE(' 2008-01-01 00:00:00'...) 15 rows selected.

可以看出，
1.設定store in，未設定預定義分區資料表空間，則預定義分區使用預設資料表空間USERS，擴充分區迴圈使用STORE IN中定義分區。
2.未設定store in, 設定預定義分區資料表空間，則預定義分區使用定義的資料表空間，擴充分區使用預設資料表空間USERS。
3.設定store in, 設定預定義分區資料表空間，則預定義分區和擴充分區均會使用STORE IN中定義分區。
4.STORE IN參數的範圍就是擴充分區，預定義分區需要明確寫出資料表空間，否則使用的使使用者預設資料表空間。

總結：
間隔分區，從常理來看，應該明確定義各分區使用的資料表空間，那麼就需要為預定義分區明確tablespace參數，而且要使用STORE IN為擴充分區定義tablespace，如果忽略任何一個，就會導致某幾個分區儲存於使用者預設的資料表空間中，這樣對分區的管理和維護就會造成一些混亂。所以從間隔分區的資料表空間分配可以看出，對於任何一種特性，都需要瞭解其使用的原理和不同用法的區別，當然實驗是最好的試金石。