跳过导航

Enhance default partition in multi-column list partitioning.

得分 0
您尚未投票。 Active

Hi

 

In multi-column list partitioning there should be more than one default partition.
e.g.

create table t1 (
areacode varchar2(5 char) not null,
customer_segment number(3) not null,
customer number(20) not null,
sale_date date,
product_group varchar2(5 char),
product  varchar2(10 char) not null,
sale  number(12,2) not null
)
PARTITION BY RANGE (SALE_DATE)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST (AREACODE,CUSTOMER_SEGMENT,PRODUCT_GROUP)
SUBPARTITION TEMPLATE
  (SUBPARTITION F_1_AA VALUES (( 'F', 1, 'AA')),
   SUBPARTITION F_1_AB VALUES (( 'F', 1, 'AB' )),
   SUBPARTITION F_1_BB VALUES (( 'F', 1, 'BB' )),
   SUBPARTITION F_1_OTHERS values (('F',1,default)),
   SUBPARTITION F_2_AA VALUES (( 'F', 2, 'AA')),
   SUBPARTITION F_2_AB VALUES (( 'F', 2, 'AB' )),
   SUBPARTITION F_2_BB VALUES (( 'F', 2, 'BB' )),
   SUBPARTITION F_2_OTHERS values (('F',2,default)),
   SUBPARTITION G_2_AA VALUES (( 'G', 2, 'AA')),
   SUBPARTITION G_2_AB VALUES (( 'G', 2, 'AB' )),
   SUBPARTITION G_2_BB VALUES (( 'G', 2, 'BB' )),
   SUBPARTITION G_2_OTHERS values (('G',2,default))
  )

  PARTITION DEFAULT_PARTITION VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

This techique would enable utilize partitioning always by some columns (AREACODE,CUSTOMER_SEGMENT) and also partition by some specific values optionally on some columns (PRODUCT_GROUP).
This is especially useful in subpartitioning where number of subpartitions must be fixed.

评论