With partition pruning, Oracle only accesses the partitions that match the from and where clauses. There are two ways to enable this:

- Specify the partition explicitly after the table name
- Have predicates on the partition column(s) in your where clause

So if your table is partitioned on c1 and your query is:

select * from t where c2 = 1;

No pruning can take place. This is because c2 = 1 could be in any of the partitions. But if your query is:

select * from t where c1 = 1;

The rows with c1 = 1 can only exist in one partition. So Oracle can ignore all the other partitions.

For example:

SQL> create table t (
  2    x int,
  3     y int
  4  ) partition by range (x) interval (10) (
  5    partition p0 values less than (10)
  6  );

Table created.

SQL>
SQL> insert into t
  2    with rws as (
  3      select rownum x from dual connect by level <= 1000
  4    )
  5      select x, x from rws;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace trace exp
SQL> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     6 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |     6 |   274   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | T    |     1 |     6 |   274   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

SQL> select * from t where x between 45 and 69;

Execution Plan
----------------------------------------------------------
Plan hash value: 1571388083

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    26 |   208 |   820   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|      |    26 |   208 |   820   (1)| 00:00:01 |     5 |     7 |
|*  2 |   TABLE ACCESS FULL      | T    |    26 |   208 |   820   (1)| 00:00:01 |     5 |     7 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=69 AND "X">=45)

SQL> select * from t where y = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 | 27538   (1)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |     8 | 27538   (1)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T    |     1 |     8 | 27538   (1)| 00:00:02 |     1 |1048575|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("Y"=1)

SQL> select * from t partition (p0);

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     9 |    54 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     9 |    54 |   274   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS FULL    | T    |     9 |    54 |   274   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Notice the Pstart and Pstop values. The query against Y includes all of these. But the others are all limited to specific partitions.

For further reading, see:

http://docs.oracle.com/database/121/VLDBG/GUID-E677C85E-C5E3-4927-B3DF-684007A7B05D.htm#VLDBG00401