Query To Be Tuned:
SELECT CITY.US_CITY_ID US_CITY_ID, CITY.CITY_NAM CITY_NAM, STATE.ADDR_STATE_CD ADDR_STATE_CD
FROM TB_CMA086_US_CITY CITY, TB_CMA004_US_STATE STATE
WHERE UPPER(SUBSTR(TRIM(CITY.CITY_NAM), 1, 4 )) = UPPER(SUBSTR(TRIM(NVL( 'KING' , '~')), 1, 4 ))
AND SUBSTR('99612-9999' , 1, 5 ) BETWEEN SUBSTR(CITY.CITY_ZIP_START_CD, 1, 9 )
AND SUBSTR(CITY.CITY_ZIP_END_CD, 1, 5 ) AND STATE.ADDR_COUNTRY_ID = CITY.ADDR_COUNTRY_ID
AND STATE.ADDR_STATE_ID = CITY.ADDR_STATE_ID

Execution Plan:
Description Cost Cardinality Bytes CPU Cost
ALL_ROWS 121 5 270 33258807
MERGE JOIN 121 5 270 33258807
TABLE ACCESS BY INDEX ROWID 2 60 720 38843
INDEX FULL SCAN 1 60 19121
SORT JOIN 119 5 210 33219964
INDEX FAST FULL SCAN 118 5 210 26921338

The City table contains 79440 and state table contains 60 records.

I created function based Index on
(UPPER(SUBSTR(TRIM(CITY_NAM),1))),
(SUBSTR(CITY_ZIP_START_CD, 1)),
(SUBSTR(CITY_ZIP_END_CD, 1))

The CPU Cost is reduced from 33258807 to 26774042. Please help me out to solve this query.