SANDBOX@devbox>;explain plan for
  2  select /*+index(t1 i4_loc)*/ *
  3      from t1
  4      where be_id = :v_be_id
  5          and hot_yn in ('Y', :v_hot_yn)
  6          and trade_id = :v_trade_id;

Explained.

SANDBOX@devbox>;select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1581337439

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     1 |    11 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE              |        |     1 |    11 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   PARTITION LIST SINGLE             |        |     1 |    11 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| T1     |     1 |    11 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX UNIQUE SCAN               | I4_LOC |     1 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------------

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

   4 - access("HOT_YN"=:V_HOT_YN AND "TRADE_ID"=TO_NUMBER(:V_TRADE_ID) AND
              "BE_ID"=TO_NUMBER(:V_BE_ID))

17 rows selected.

SANDBOX@devbox>;explain plan for
  2  select *
  3      from t1
  4      where be_id = :v_be_id
  5          and hot_yn in ('Y', :v_hot_yn)
  6          and trade_id = :v_trade_id;

Explained.

SANDBOX@devbox>;select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 874832600

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     1 |    11 |     3   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T1            |     1 |    11 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                | SYS_C00146066 |     1 |       |     2   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

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

   1 - filter("BE_ID"=TO_NUMBER(:V_BE_ID) AND ("HOT_YN"=:V_HOT_YN OR "HOT_YN"='Y'))
   2 - access("TRADE_ID"=TO_NUMBER(:V_TRADE_ID))

15 rows selected.

CREATE TABLE T1 (
BE_ID NUMBER NOT NULL,
HOT_YN CHAR(1) DEFAULT 'Y' NOT NULL CHECK (HOT_YN IN ('Y', 'N')),
TRADE_ID NUMBER PRIMARY KEY,
DUMMY01 CHAR(2000),
DUMMY02 CHAR(2000),
...
DUMMY50 CHAR(2000)
)
PARTITION BY LIST (BE_ID)
SUBPARTITION BY LIST (HOT_YN)
(  
  PARTITION BE_1 VALUES (1)
  ( SUBPARTITION BE_1_HOT_Y VALUES ('Y'),
    SUBPARTITION BE_1_HOT_N VALUES ('N') ),
  PARTITION BE_2 VALUES (2)
  ( SUBPARTITION BE_2_HOT_Y VALUES ('Y'),
    SUBPARTITION BE_2_HOT_N VALUES ('N') ),
...
  PARTITION BE_10 VALUES (10)
  ( SUBPARTITION BE_10_HOT_Y VALUES ('Y'),
    SUBPARTITION BE_10_HOT_N VALUES ('N') ),
  PARTITION BE_DEFAULT VALUES (DEFAULT)
  ( SUBPARTITION BE_DEFAULT_HOT_Y VALUES ('Y'),
    SUBPARTITION BE_DEFAULT_HOT_N VALUES ('N') )
)
ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX I4_LOC ON T1(BE_ID, HOT_YN, TRADE_ID) LOCAL;