分割槽欄位VALUES LESS THAN時間格式及轉換

lusklusklusk發表於2017-08-11
分割槽欄位的屬性TIMESTAMP(3)或date,可以如下
alter table XX add PARTITION p01 VALUES LESS THAN (to_date('2003-1-1','yyyy-mm-dd'))
CREATE TABLE customer701
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );

CREATE TABLE customer702
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       TIMESTAMP(3)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );



alter table XX add PARTITION p01 VALUES LESS THAN (timestamp '2016-08-01 00:00:00.000')
CREATE TABLE customer7012
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (timestamp '2016-08-01 00:00:00.000')
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );

CREATE TABLE customer7022
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       TIMESTAMP(3)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (timestamp '2016-08-01 00:00:00.000')
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );



分割槽欄位的屬性varchar2(20),可以如下
alter table XX add PARTITION p01 VALUES LESS THAN (to_date('2004-1-1','yyyy-mm-dd'))
CREATE TABLE customer703
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       varchar2(20)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30126024/viewspace-2143425/,如需轉載,請註明出處,否則將追究法律責任。

相關文章