Code:
SELECT  a.person_nr 
                            FROM    JOB_QUEUE b,
                                    REGIMEN_PRES rp,
                                    PERSON a
                            WHERE   a.person_nr = b.person_nr 
                                AND a.person_nr = rp.person_nr 
                                AND b.job_queue_type_nr = 4 
                                AND b.status_code = 'FNSH'
                                AND (a.status_code = 'ACTV' or 
                                     a.status_code = 'CHNG'

      37115  rows processed

Elapsed: 00:00:34.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2435 Card=382725 Bytes=8802675)

   1    0   HASH JOIN (Cost=2435 Card=382725 Bytes=8802675)
   2    1     HASH JOIN (Cost=1981 Card=69850 Bytes=1257300)
   3    2       TABLE ACCESS (FULL) OF 'PERSON' (Cost=673 Card=33831 Bytes=338310)
   4    2       TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1266 Card=158302 Bytes=1266416)
   5    1     INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)





Statistics
----------------------------------------------------------
         35  recursive calls
          0  db block gets
      13929  consistent gets
          0  physical reads
          0  redo size
     424699  bytes sent via SQL*Net to client
      27709  bytes received via SQL*Net from client
       2476  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      37115  rows processed


37115 rows selected.

----------------------------------------------------------------------------------------------------------------------------------------

After creating composite index


SELECT  a.person_nr 
                            FROM    JOB_QUEUE b,
                                    REGIMEN_PRES rp,
                                    PERSON a
                            WHERE   a.person_nr = b.person_nr 
                                AND a.person_nr = rp.person_nr 
                                AND b.job_queue_type_nr = 4 
                                AND b.status_code = 'FNSH'
                                AND (a.status_code = 'ACTV' or 
                                     a.status_code = 'CHNG'
Elapsed: 00:00:02.68

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1479 Card=397716 Bytes=7556604)
   1    0   HASH JOIN (Cost=1479 Card=397716 Bytes=7556604)
   2    1     HASH JOIN (Cost=1028 Card=72586 Bytes=1016204)
   3    2       TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
   4    2       TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=164400 Bytes=986400)
   5    1     INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13918  consistent gets
          0  physical reads
          0  redo size
     528694  bytes sent via SQL*Net to client
      27713  bytes received via SQL*Net from client
       2476  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      37115  rows processed
Hrishy thanks a lot,Plan doesn't changes.but execution time remarkable.Another subquery has no change.
please suggest to change NOT LIKE predicate.