There is table T,
partitioned by range on column C1,
subpartitioned by list on column C2.
There is LOCAL index on table t(JustACol, C1).
There is query:
select --+ index(t idx_2)
*
from t
where C1 = 'A'
and C2 = 'B'
Question: why columns PStart and PStop in the execution plan below consists of "KEY".
Oracle clearly knows which subpartitions will be used, or not ?
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5688K| 1448M| 873K (7)| 00:03:49 | | |
| 1 | PARTITION RANGE SINGLE | | 5688K| 1448M| 873K (7)| 00:03:49 | 8 | 8 |
| 2 | PARTITION LIST SINGLE | | 5688K| 1448M| 873K (7)| 00:03:49 | KEY | KEY |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 5688K| 1448M| 873K (7)| 00:03:49 | 200 | 200 |
| 4 | INDEX FULL SCAN | IDX_2 | 508K| | 33610 (15)| 00:00:09 | 200 | 200 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C1"='A')