特定子分区的分区交换加载
查看归档策略 - 我们必须归档特定的数据集。
我正在考虑使用分区交换，而不是插入/删除例程。
待归档表按日期进行区间范围分区，并按国家/地区进行列表子分区。
我想针对特定国家/地区进行交换。
create table
test_table
(tbl_id number,
country varchar2(2),
sales_dt date,
volume number)
partition by range (sales_dt) interval (NUMTOYMINTERVAL(1,'Month'))
subpartition by list (country)
Subpartition template
(subpartition p_ireland values ('IR'),
subpartition p_france values ('FR'),
subpartition p_other values (DEFAULT))
(partition before_2008 values less than (to_date('01-JAN-2008','DD-MON-YYYY')));
加载的数据正确落入分区和子分区。所有分区名称都是系统生成的。
当我对所有“FR”子分区进行分区交换时，我无法确定逻辑。
使用
Alter table test_table
exchange subpartition system_generated_name
with table TEST_TABLE_ARCH;
I 可以交换特定的“已知”子分区。
我知道您可以在 Oracle 11g 中使用“for”逻辑，但无法使语法正常工作。
有什么想法吗？
Looking at an archive strategy - where we have to archive a specific dataset.
Rather than a insert/delete routine - I was thinking of using partition exchange.
The to-be archived table is interval range partitioned on date, with a list subpartition on country.
It is for specific countries that I want to partition exchange.
create table
test_table
(tbl_id number,
country varchar2(2),
sales_dt date,
volume number)
partition by range (sales_dt) interval (NUMTOYMINTERVAL(1,'Month'))
subpartition by list (country)
Subpartition template
(subpartition p_ireland values ('IR'),
subpartition p_france values ('FR'),
subpartition p_other values (DEFAULT))
(partition before_2008 values less than (to_date('01-JAN-2008','DD-MON-YYYY')));
The data loaded falls into the partitions and subpartitions correctly. All the partitions names are system generated.
When I come to partition exchange for all the 'FR' subpartitions- I can't determine the logic.
Using
Alter table test_table
exchange subpartition system_generated_name
with table TEST_TABLE_ARCH;
I can swap out a specific 'known' subpartition.
I know you can use the 'for' logic with Oracle 11g but can't get the syntax to work.
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我们过去对此进行了测试，并相信我们必须想出一个程序来正确执行此操作。目标是进行两次分区交换：一次在源表和空交换表之间，第二次在现在填充的交换表和存档表之间。
先决条件：创建一个空交换表以与源表进行分区交换。另外，创建一个也将被分区的存档表。
高级流程：
（结果：源表中的空分区，交换表中的归档分区）
（结果：原始源表分区现在是存档表中的一个分区，交换表又恢复为空）
我们从未对此进行编码，因此此过程可能会需要调整，但我们认为如果我们采取这样的策略，这就是我们必须要做的。
We tested this in the past and believed that we had to come up with a procedure to do it right. The goal is to do two partition swaps: one between the source table and your empty swap table, and a second one between your now-populated swap table and the archive table.
Prereqs: Create an empty swap table to do the partition swap with your source table. Also, create an archive table that will be partitioned as well.
High-level process:
(result: empty partition in source table, archived partition in swap table)
(result: original source table partition is now a partition in your archive table and swap table is back to being empty)
We never coded this so this process may need tweaking but we think this is what we would have had to do if we pursued such a strategy.