query tuning

[ams-jamali]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        |  Name                     	| Rows  | Bytes   |TempSpc 	| Cost  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT           	|                            	|   399 | 23142|       	|  5337 |
| 1 |  SORT ORDER BY             	|                               	|   399 | 23142|       	|  5337 |
|*2 |   HASH JOIN SEMI        	|                                	|   399 | 23142|       	|  5326 |
|*3 |    HASH JOIN               	|                                    	|   399 | 21546|       	|  2570 |
|*4 |     HASH JOIN              	|                                   	|  7084 |  290K|       	|  1810 |
|*5 |      TABLE ACCESS FULL     	| PERSON            		|  7084 |  172K|       	|   703 |
| 6 |      VIEW                    | VW_SQ_1                      	| 87401 | 1450K|       	|  1092 |
| 7 |       SORT GROUP BY        	|                             	| 87401 |  682K|  5360K	|  1092 |
|*8 |        INDEX FAST FULL SCAN	| REGIMEN_PRES_PK   	|   184K|  1438K|       	|   413 |
|*9 |     TABLE ACCESS FULL      	| REGIMEN_PRES       	| 61385 |   719K|       	|   745 |
|10 |    VIEW                    	| VW_NSO_2                   	|   209K|   817K|       	|  2753 |
|* 11 |     HASH JOIN              	|                            	|   209K|  4495K|  1080K	|  2753 |
|* 12 |      HASH JOIN             	|                            	| 36761 |   646K|       	|  2190 |
|* 13 |       TABLE ACCESS FULL    	| JOB_QUEUE                  	| 36761 |   358K|       	|  1466 |
|* 14 |       TABLE ACCESS FULL    	| PERSON                     	| 40479 |   316K|       	|   703 |
|  15 |      INDEX FAST FULL SCAN  	| REGIMEN_PRES_QX28  	|   552K|  2158K|       	|   373 |

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


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;

Query copied from previous thread
thnx Hrishy