如何在oracle的单个select语句中包含多个分区 [英] How to include more than one partition in a single select statement in oracle

问题描述

create table agg_summary (period date, lvl_id number);

已为 lvl_id 创建了分区，其中包括 1,2,3 作为每个 id 的单独分区.

Partition has been created for lvl_id which includes 1,2,3 as a separate partition for each id.

如何访问 agg_summary 将 1 和 2 放在一起?

How to access agg_summary to have 1 and 2 together?

推荐答案

至少有三种方法可以从特定分区中选择数据.请参阅手册以获取对语法.

There are at least three ways to select data from specific partitions. See the manual for a thorough description of the syntax.

create table agg_summary (period date, lvl_id number)
partition by list (lvl_id)
(
    partition p1 values (1),
    partition p2 values (2),
    partition p3 values (3)
);

--#1: Normal predicate:
select * from agg_summary where lvl_id in (1,2);

--#2: Partition_extended_name:
select * from agg_summary partition (p1)
union all
select * from agg_summary partition (p2);

--#3: Partition_excension_clause:
select * from agg_summary partition for (1)
union all
select * from agg_summary partition for (2);

99.9% 的时间选项 #1 应该足够了.Oracle 将自动确定使用哪些分区并正确修剪.对于修剪无法正常工作的情况，或者根据分区名称或键进行选择更合乎逻辑，选项 #2 或 #3 应该有效.

99.9% of the time option #1 should be sufficient. Oracle will automatically determine which partitions are used and will prune correctly. For cases where pruning doesn't work correctly, or it's more logical to select based on the partition name or key, options #2 or #3 should work.

这篇关于如何在oracle的单个select语句中包含多个分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！