create table dropme_t (id, dt)
partition by range(dt)
(partition pdec2017 values less than (date'2018-01-01')
,partition pjan2018 values less than (date'2018-02-01')
,partition pfeb2018 values less than (date'2018-03-01')
)
as
select to_number(reverse(to_char(rownum,'fm9999999999999999999999'))), date'2018-02-01'-30+mod(rownum,40) from dual connect by level < 100000
;
Table created

create index dropme_t$glb$nprfx_id on dropme_t(id); -- global non-prefixed index

Index created

explain plan for select * from dropme_t where id = :1 and dt > :2;
Explained

select * from table(dbms_xplan.display(format => 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3965172464
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |    45 |   990 |    77   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| DROPME_T              |    45 |   990 |    77   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | DROPME_T$GLB$NPRFX_ID |   357 |       |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DROPME_T@SEL$1
   2 - SEL$1 / DROPME_T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DT">:2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - access("ID"=TO_NUMBER(:1))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "ID"[NUMBER,22], "DT"[DATE,7]
   2 - "DROPME_T".ROWID[ROWID,10], "ID"[NUMBER,22]
Note
-----
   - dynamic sampling used for this statement (level=2)
31 rows selected

-- А теперь изменим предикат:
explain plan for select * from dropme_t where id = 18 and dt > :2;
Explained

select * from table(dbms_xplan.display(format => 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1604903573
-----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |  4461 | 98142 |    79   (2)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|          |  4461 | 98142 |    79   (2)| 00:00:01 |   KEY |     3 |
|*  2 |   TABLE ACCESS FULL      | DROPME_T |  4461 | 98142 |    79   (2)| 00:00:01 |   KEY |     3 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DROPME_T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">=18 AND "DT">:2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "ID"[NUMBER,22], "DT"[DATE,7]
   2 - "ID"[NUMBER,22], "DT"[DATE,7]
Note
-----
   - dynamic sampling used for this statement (level=2)
30 rows selected

-- А теперь изменим предикат:
explain plan for select * from dropme_t where id >= 18 and dt > :2;