Oracle: how to turn an IN subquery into hash join

 The goal is to replace the nested loop, that is taking long, by a hash join.

Original query:

SELECT /*+ PARALLEL(M,8) */ 

my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM

LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL

019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_

TXT,'R'  

FROM my_tab M  WHERE my_tab_BAN IN 

(SELECT /*+ PARALLEL(HS,4) */

BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND

CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S')  AND

my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;

Modified query:

SELECT /*+ PARALLEL(M,8) FULL(M) use_hash(M)  */ 

my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM

LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL

019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_

TXT,'R'  

FROM my_tab M  WHERE my_tab_BAN IN 

(SELECT /*+ PARALLEL(HS,4) */

BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND

CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S')  AND

my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;