How force the database to use function based index in Oracle

Question

Here is the code, and I'm trying to create a function based index.

But Oracle did not apply it when computing the query results.

Things works fine when I'm not using two tables

Answer 1

Use an INDEX hint:

SELECT SUM(AMOUNT)
  FROM (SELECT /*+ INDEX(P SERVICE_INDEX) */
               P.*, E.* 
          FROM PURCHASE P
          INNER JOIN EMP E
            ON E.EMPNO = P.SERVEDBY
          WHERE INSTR(P.SERVICETYPE, 'Software') = 0) 
  WHERE DEPTNO = 50

Note that there may be very good reasons for the optimizer to ignore your index.