Oracle Database Discussions

Dear friends, I am running a query on default optimizer mode (all_rows) but its not picking the right execution plan nor the right index but if changing optimizer mode into first_rows, its running insteadly. Tables statistics for concern tables are upto date. Same query was running fine some days back.

Any idea about this behavior ?

My culprit query as follow:
SELECT *
FROM (SELECT *
FROM (SELECT /*+ parallel(h,10) */
MSISDN,
IMSI,
SUBSCRIBER_TYPE,
CASE
WHEN INSTR(IN_SERVICE_NAMES, 'MTCIN') = 0 THEN
''
WHEN INSTR(IN_SERVICE_NAMES, 'MTCIN') <> 0 THEN
SUBSTR(IN_SERVICE_NAMES,
INSTR(IN_SERVICE_NAMES, 'MTCIN') + 5,
2)
END MTC
FROM RDMBKPIPRDAPP.HLS_OUT H
WHERE LOAD_PROC_ID = '20112501'
AND LOADING_COUNT = '1'
AND SUBSCRIBER_TYPE = 'Prepaid')) A
INNER JOIN (SELECT /*+ fu parallel(i,10) */
MSISDN, IN_ID, LTRIM(IN_ID, '0') IN_INID
FROM RDMBKPIPRDAPP.IN_OUT I
WHERE LOAD_PROC_ID = '20112501'

) B ON A.MSISDN = B.MSISDN
WHERE A.MOC <> B.IN_ID

Regards
Irfan Ahmad