Oracle 多列分区与使用子分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4378554/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle multicolumn partitioning vs using a subpartition
提问by VinceJS
Apart from the obvious, can anyone explain the what is different between multicolumn partitioning and using a subpartition? And which one is better for a OLTP scenario? For details, see Managing Partitioned Tables and Indexesin the Oracle Database Administrator's Guide.
除了显而易见的，谁能解释多列分区和使用子分区之间的区别？哪个更适合 OLTP 场景？有关详细信息，请参阅Oracle 数据库管理员指南中的管理分区表和索引。
A (dumb) example of a table partitioned on multiple columns is:
在多列上分区的表的（哑）示例是：
CREATE TABLE demo1
(
year NUMBER,
month NUMBER,
day NUMBER,
instance NUMBER, /* assuming this can only be 1 or 2 */
other1 VARCHAR2(50),
other2 VARCHAR2(50),
other3 VARCHAR2(50)
)
PARTITION BY RANGE (year,instance)
(
PARTITION data_2009_inst1 VALUES less than (2009,2) TABLESPACE data_2009,
PARTITION data_2009_inst2 VALUES less than (2009,3) TABLESPACE data_2009,
PARTITION data_2010_inst1 VALUES less than (2010,2) TABLESPACE data_2010,
PARTITION data_2010_inst2 VALUES less than (2010,3) TABLESPACE data_2010,
PARTITION data_2011_inst1 VALUES less than (2011,2) TABLESPACE data_2011,
PARTITION data_2011_inst2 VALUES less than (2011,3) TABLESPACE data_2011
);
Similarly, example of a subpartitioned table is:
同样，子分区表的示例是：
CREATE TABLE demo2
(
year NUMBER,
month NUMBER,
day NUMBER,
instance NUMBER, /* assuming this can only be 1 or 2 */
other1 VARCHAR2(50),
other2 VARCHAR2(50),
other3 VARCHAR2(50)
)
PARTITION BY RANGE (year)
SUBPARTITION BY LIST (instance) /* Cannot subpartition by range in 10gR2 */
SUBPARTITION template
(
SUBPARTITION i1 VALUES (1),
SUBPARTITION i2 VALUES (2),
SUBPARTITION ix VALUES (DEFAULT)
)
(
PARTITION data_2009 VALUES less than (2010) TABLESPACE data_2009,
PARTITION data_2010 VALUES less than (2011) TABLESPACE data_2010,
PARTITION data_2011 VALUES less than (2012) TABLESPACE data_2011
);
Now what is the difference between these tables? Are they not "logically" the same? I know its far easier to add partitions to demo2 as you need to split partitions on demo1 to get more partitions as time passes by. Which on is better in an OLTP scenario?
现在这些表有什么区别？它们在“逻辑上”不一样吗？我知道向 demo2 添加分区要容易得多，因为您需要在 demo1 上拆分分区以随着时间的推移获得更多分区。在 OLTP 场景中哪个更好？
On a side note, the reason I am partitioning on the INSTANCE number has to do with Oracle RAC. I am trying to create an "instance affinity" to stop "hot block" from slowing down the database as these need be sent across the interconnect between the RAC nodes. (We have empirically proved that this does make a difference in our testing).
附带说明一下，我在 INSTANCE 编号上进行分区的原因与 Oracle RAC 有关。我正在尝试创建“实例关联”以阻止“热块”减慢数据库速度，因为这些需要通过 RAC 节点之间的互连发送。（我们已经凭经验证明这确实对我们的测试产生了影响）。
回答by Tony Andrews
There probably isn't any difference in your case, but in general sub-partitioning allows you to partition in 2 different ways, such as range-hash, range-list. Your sub-partition example is range-list, but equivalent to the single-level range partitioning. However, you could not use a single-level if your sub-partitioning was like this example from the doc you linked:
您的情况可能没有任何区别，但通常子分区允许您以两种不同的方式进行分区，例如范围哈希、范围列表。您的子分区示例是范围列表，但等效于单级范围分区。但是，如果您的子分区类似于您链接的文档中的这个示例，则不能使用单级：
ALTER TABLE quarterly_regional_sales
ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
(
SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
);
回答by shonky linux user
One advantage of sub-partitions is that they allow individual fine-grained management of the sub-partitions. For example in a data archive table lets say there are different retention requirements based not only on the date, but another value as well.
子分区的优点之一是它们允许对子分区进行单独的细粒度管理。例如，在数据存档表中，可以说存在不同的保留要求，不仅基于日期，还基于另一个值。
Using your example perhaps you are required to keep data with value instance = 1 for 7 years, but data with instance = 2 can be discarded after 4 years. Sub-partitioning would allows you to drop the sub-partitions containing data with instance = 2 independently of the other values.
使用您的示例，您可能需要将 instance = 1 值的数据保留 7 年，但 instance = 2 的数据可以在 4 年后丢弃。子分区允许您独立于其他值删除包含 instance = 2 数据的子分区。
