How to include more than one partition in a single select statement in oracle

Question

create table agg_summary (period date, lvl_id number);

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

How to access agg_summary to have 1 and 2 together?

Answer 1

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% 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.

Answer 2

When using hash as partitioning option, you may consider "ora_hash()" function as criterio in WHERE condition of your qry.

So, if hashing against table CUSTOMERS with CUSTOMER_ID the partitioning key for 32 partitions and needed partitions are 1st, 2nd and 3rd, your WHERE condition should look like:

select * from CUSTOMERS C where MOD(ORA_HASH(CUSTOMER_ID),32) in (1,2,3);