SELECT rp.person_nr,rp.regimen_sequence,rp.regimen_type_code FROM REGIMEN_PRES rp,
PERSON p WHERE regimen_type_code = 'CD' AND prescription_status_code= 'FNSH'
AND rp.person_nr = p.person_nr and p.email not like '%test%' AND
regimen_sequence = (SELECT MAX(regimen_sequence) FROM REGIMEN_PRES r
WHERE r.person_nr = p.person_nr AND regimen_type_code = 'CD')
AND p.person_nr IN (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'))
ORDER BY p.person_nr;

Here is the output of plan table:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5059 Card=516 Bytes=29928)
1 0 SORT (ORDER BY) (Cost=5059 Card=516 Bytes=29928)
2 1 HASH JOIN (SEMI) (Cost=5046 Card=516 Bytes=29928)
3 2 HASH JOIN (Cost=2413 Card=516 Bytes=27864)
4 3 HASH JOIN (Cost=1684 Card=7084 Bytes=297528)
5 4 TABLE ACCESS (FULL) OF 'PERSON' (Cost=703 Card=7084 Bytes=177100)
6 4 VIEW OF 'VW_SQ_1' (Cost=969 Card=63454 Bytes=1078718)
7 6 SORT (GROUP BY) (Cost=969 Card=63454 Bytes=507632)
8 7 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_PK' (UNIQUE) (Cost=367 Card=176165 Bytes=1409320)
9 3 TABLE ACCESS (FULL) OF 'REGIMEN_PRES' (Cost=715 Card=58722 Bytes=704664)
10 2 VIEW OF 'VW_NSO_2' (Cost=2628 Card=342716 Bytes=1370864)
11 10 HASH JOIN (Cost=2628 Card=342716 Bytes=7539752)
12 11 HASH JOIN (Cost=2094 Card=42816 Bytes=770688)
13 12 TABLE ACCESS (FULL) OF 'PERSON' (Cost=703 Card=40479 Bytes=323832)
14 12 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1369 Card=42817 Bytes=428170)
15 11 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_QX28' (NON-UNIQUE) (Cost=348 Card=528495 Bytes=2113980)

I have index on person,job_queue & regimen_pres tables on that particular column also.Why it is accessing Full table scan.
please help.,