如何在Oracle 11g中创建日和时分区？

Question

我正在使用Oracle-11G数据库。

我正在尝试创建day智能分区，并在每小时级别上再次创建子分区。

下面是我正在使用的脚本

CREATE TABLE test_shipments
( order_date    DATE NOT NULL
, delivery_date DATE NOT NULL
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY RANGE (order_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('2006-08-01','YYYY-MM-DD'))
  ( SUBPARTITION p06_jul_e1 VALUES LESS THAN (TO_DATE('2006-08-01 12:00:00','YYYY-MM-DD hh24:mi:ss')) 
  ,SUBPARTITION p06_jul_e2 VALUES LESS THAN (TO_DATE('2006-08-01 13:00:00','YYYY-MM-DD hh24:mi:ss')) 
  ,SUBPARTITION p06_jul_e3 VALUES LESS THAN (TO_DATE('2006-08-01 14:00:00','YYYY-MM-DD hh24:mi:ss')) 
  )
  );

我希望每天都有分区，在那一天的每一个小时都有分区，上面的脚本每天只创建分区。没有创建每小时的子分区。有人能指点一下吗？

Answer 1

为什么不按小时范围创建简单的分区？示例：PARTITION BY RANGE (order_date) INTERVAL (INTERVAL '1' HOUR)

不管怎么说，这个很管用。但是，您应该使用TIMESTAMP数据类型而不是DATE。对于小时值，您必须创建一个虚拟列。

CREATE TABLE test_shipments (
   order_date    TIMESTAMP(0) NOT NULL,
   order_date_hour NUMBER GENERATED ALWAYS AS (EXTRACT(HOUR FROM order_date)), 
   delivery_date DATE NOT NULL)
PARTITION BY RANGE (order_date)
INTERVAL (INTERVAL '1' DAY)
SUBPARTITION BY LIST (order_date_hour)
SUBPARTITION TEMPLATE
  (SUBPARTITION HOUR00 VALUES (0),
   SUBPARTITION HOUR01 VALUES (1),
   SUBPARTITION HOUR02 VALUES (2),
   SUBPARTITION HOUR03 VALUES (3),
   ...
   SUBPARTITION HOUR22 VALUES (22),
   SUBPARTITION HOUR23 VALUES (23)   
  )
(  
  PARTITION P_20150101 VALUES LESS THAN (TIMESTAMP'2015-01-01 00:00:00')  
);

如果强制使用DATE数据类型，则可以这样做：

CREATE TABLE test_shipments (
   order_date    DATE NOT NULL,
   order_date_hour NUMBER GENERATED ALWAYS AS (TO_CHAR(order_date, 'HH24')), 
   delivery_date DATE NOT NULL)
PARTITION BY RANGE (order_date)
INTERVAL (INTERVAL '1' DAY)
SUBPARTITION BY LIST (order_date_hour)
SUBPARTITION TEMPLATE
  (SUBPARTITION HOUR00 VALUES (0),
   SUBPARTITION HOUR01 VALUES (1),
   SUBPARTITION HOUR02 VALUES (2),
   SUBPARTITION HOUR03 VALUES (3),
   ...
   SUBPARTITION HOUR22 VALUES (22),
   SUBPARTITION HOUR23 VALUES (23)   
  )
(  
  PARTITION P_20150101 VALUES LESS THAN (DATE '2015-01-01')  
);

也许你想把晚上的时间分组：

(
SUBPARTITION HOUR_NIGHT VALUES (0,1,2,3,4,5,6, 19,20,21,22,23),
SUBPARTITION HOUR07 VALUES (7),
SUBPARTITION HOUR08 VALUES (8),
SUBPARTITION HOUR11 VALUES (11),
...
SUBPARTITION HOUR17 VALUES (17),
SUBPARTITION HOUR18 VALUES (18)
)