how to select data from multiple partitions in oracle table

Question

I am trying to select data from multiple partitions in a partitioned table. It is working for single partition ( select * from table partition(ParititonName), but is not to able to select multiple partitions ( select * from table partitions(Part1,part2). Could you please let me know how to select multiple partitions in a single query.

Answer 1

If you need to address the partition names explicitely in your query - which is not a typical use case (as you often use the WHERE predicate for partition pruning) - but could be the case for hash partitioning, you may use UNION ALLto access more partitions.

select * from TAB partition (Part1) 
union all
select * from TAB partition (Part2);

The execution plan shows (see the columns Pstart and Pstop) that only the partitions 1 and 2 are accessed.

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     2 |    86 |     4   (0)| 00:00:01 |       |       |
|   1 |  UNION-ALL                  |      |       |       |            |          |       |       |
|   2 |   PARTITION HASH SINGLE     |      |     1 |    43 |     2   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS STORAGE FULL| TAB  |     1 |    43 |     2   (0)| 00:00:01 |     1 |     1 |
|   4 |   PARTITION HASH SINGLE     |      |     1 |    43 |     2   (0)| 00:00:01 |     2 |     2 |
|   5 |    TABLE ACCESS STORAGE FULL| TAB  |     1 |    43 |     2   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------

Answer 2

You don't have to care about that. "Partition" is related to "storage", Oracle takes care about it. All you need is to run any query you want, e.g. if EMP table is partitioned on DEPTNO column (every department goes to its own partition), you'd still run

select deptno, empno, ename, sal
from emp
where deptno in (10, 20);

You don't specify a partition.