从oracle分区中排除值 [英] Exclude values from oracle partition

问题描述

我想在oracle中使用Composite Range-List分区.

I want to use a Composite Range-List Partitioning in oracle.

使用范围对我来说很清楚.棘手的部分在列表分区上，这是我有问题的地方.

Using a range is pretty clear to me. The tricky part is on the list partition and this is where i have the question.

让我们假设我们有一个包含各大洲的专栏.我希望列表分区包含 NOT 欧洲的所有大洲.这里的问题是，我无法创建包括除欧洲以外的所有其他大洲的列表分区，因为有时我可能会添加新的大洲.

Let's assume we have a column with continents. I want the list partition that to include all the continents that are NOT Europe. The problem here is that i cannot create a list partition including all other continents except of Europe, as at some point i might add new continents.

这样可能吗?

推荐答案

来自Oracle 文档

使用列表分区时，之间没有明显的顺序感 分区.您还可以在行中指定默认分区 没有映射到任何其他分区的映射.

With list partitioning, there is no apparent sense of order between partitions. You can also specify a default partition into which rows that do not map to any other partition are mapped.

基于上述内容，您可以创建两个分区，一个分区用于欧洲，另一个分区用于其他分区，并将后者也设置为默认分区. 这样，您可以将欧洲的数据放在一个分区中，而除欧洲以外的任何数据都转到默认分区.

Based on above, you can create two partitions, one for Europe and another for others and set latter as default too. This way you can have your data of Europe in one partition and anything other than europe goes to default partition.

以下是示例

CREATE TABLE sample_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
          TABLESPACE tbs_1
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4
         ),
       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
          TABLESPACE tbs_2
         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
          TABLESPACE tbs_3
         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4
         ),
       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
          TABLESPACE tbs_4
      );

源

这篇关于从oracle分区中排除值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！