Search notes:

Oracle PLAN_TABLE: column PARTITION_START and PARTITION_STOP

The PLAN_TABLE columns PARTITION_START and PARTITION_STOP are used in combination with partition pruning.
When using dbms_xplan, the values of partition_start and partition_stop are displayed in the columns PSTART and PSTOP.

List partitioned table

For demonstration purposes, a list partitioned table is created: 
create table tq84_part_tab (
   val    number,
   par    varchar(1)
)
partition by list (par) (
   partition tq84_part_a values ('A'),
   partition tq84_part_b values ('B'),
   partition tq84_part_c values ('C'),
   partition tq84_part_d values ('D'),
   partition tq84_part_e values ('E'),
   partition tq84_part_f values ('F')
);

Number

When a partition can be identified uniquely, the partition number is the value of partition_start and partition_stop. 
explain plan for
select
   val
from
   tq84_part_tab
where
   par = 'D';
--
-- ---------------------------------------------------------------
-- | Id  | Operation             | Name          | Pstart| Pstop |
-- ---------------------------------------------------------------
-- |   0 | SELECT STATEMENT      |               |       |       |
-- |   1 |  PARTITION LIST SINGLE|               |     4 |     4 |
-- |   2 |   TABLE ACCESS FULL   | TQ84_PART_TAB |     4 |     4 |
-- ---------------------------------------------------------------
In the above case, the details of the partition can be found in the data dictionary view user_tab_partitions: 
select *
from
   user_tab_partitions
where
   table_name         = 'TQ84_PART_TAB' and
   partition_position =  4;

From … to

Using between causes partition_start and partition_stop to be filled with the first and last partition number of the partitions that match the values in the between clause. 
explain plan for
select
   val
from
   tq84_part_tab
where
   par between 'B' and 'D';

select * from table(dbms_xplan.display(format => 'basic +partition'));
--
-- -----------------------------------------------------------------
-- | Id  | Operation               | Name          | Pstart| Pstop |
-- -----------------------------------------------------------------
-- |   0 | SELECT STATEMENT        |               |       |       |
-- |   1 |  PARTITION LIST ITERATOR|               |     2 |     4 |
-- |   2 |   TABLE ACCESS FULL     | TQ84_PART_TAB |     2 |     4 |
-- -----------------------------------------------------------------

KEY(I)

explain plan for
select
   val
from
   tq84_part_tab
where
   par in ('B', 'D');
--
-- ---------------------------------------------------------------
-- | Id  | Operation             | Name          | Pstart| Pstop |
-- ---------------------------------------------------------------
-- |   0 | SELECT STATEMENT      |               |       |       |
-- |   1 |  PARTITION LIST INLIST|               |KEY(I) |KEY(I) |
-- |   2 |   TABLE ACCESS FULL   | TQ84_PART_TAB |KEY(I) |KEY(I) |
-- ---------------------------------------------------------------
Note, there is also KEY(SQ) and KEY(AP) where SQ stands for subquery, and AP for and pruning.

INVALID

There is no partition where par = 'X', therefore, the following statement fills INVALID into Pstart and Pstop. 
explain plan for
select
   val
from
   tq84_part_tab
where
   par = 'X';

select * from table(dbms_xplan.display(format => 'basic +partition'));
--
-- --------------------------------------------------------------
-- | Id  | Operation            | Name          | Pstart| Pstop |
-- --------------------------------------------------------------
-- |   0 | SELECT STATEMENT     |               |       |       |
-- |   1 |  PARTITION LIST EMPTY|               |INVALID|INVALID|
-- |   2 |   TABLE ACCESS FULL  | TQ84_PART_TAB |INVALID|INVALID|
-- --------------------------------------------------------------

Sub-partitioned table

drop table tq84_part_intv_rng purge;

create table tq84_part_intv_rng (
    num    number,
    grp    varchar2(1),
    val    number
)
partition by range (num) interval (100)
    subpartition by list (grp)
        subpartition template (
          subpartition sub_part_a values ('A'),
          subpartition sub_part_b values ('B'),
          subpartition sub_part_c values ('C')
        )
(
  partition values less than ( 100 ) 
);


insert into tq84_part_intv_rng values (888, 'B', 42);
insert into tq84_part_intv_rng values (888, 'A', 99);
insert into tq84_part_intv_rng values (333, 'C', 78);
insert into tq84_part_intv_rng values (555, 'A', -1);
insert into tq84_part_intv_rng values (111, 'B', -1);
insert into tq84_part_intv_rng values (  0, 'A',  0);

select
   prt.high_value             prt_hv,
   sub.high_value             sub_hv,
   prt.partition_position     prt_pos,
   sub.subpartition_position  sub_pos,
   sub.subpartition_name,
   sub.partition_name,
   prt.subpartition_count,
   prt.composite
from
   user_tab_subpartitions sub                                               join
   user_tab_partitions    prt on sub.partition_name = prt.partition_name
where
   sub.table_name = 'TQ84_PART_INTV_RNG';

explain plan for
select
   val
from
   tq84_part_intv_rng
where
   num =   0   and
   grp =  'A';
select * from table(dbms_xplan.display(format=>'basic +partition'));

explain plan for
select
   val
from
   tq84_part_intv_rng
where
   num =   0   and
   grp =  'B';
select * from table(dbms_xplan.display(format=>'basic +partition'));

explain plan for
select
   val
from
   tq84_part_intv_rng
where
   num =   0   and
   grp =  'C';
select * from table(dbms_xplan.display(format=>'basic +partition'));


explain plan for
select
   val
from
   tq84_part_intv_rng
where
   num = 111   and
   grp =  'A';
select * from table(dbms_xplan.display(format=>'basic +partition'));

explain plan for
select
   val
from
   tq84_part_intv_rng
where
   num = 111   and
   grp =  'B';
select * from table(dbms_xplan.display(format=>'basic +partition'));

explain plan for
select
   val
from
   tq84_part_intv_rng
where
   num = 111   and
   grp =  'C';
select * from table(dbms_xplan.display(format=>'basic +partition'));

Index