Hi

I wish you would have learned how to post and ask a question

Code:
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;
Plan is

Code:
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 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("P"."PERSON_NR"="VW_NSO_2"."$nso_col_1")
3 - access("RP"."PERSON_NR"="P"."PERSON_NR" AND "RP"."REGIMEN_SEQUENCE"="VW_SQ_1"."VW_COL_1")
4 - access("VW_SQ_1"."PERSON_NR"="P"."PERSON_NR")
5 - filter("P"."EMAIL" NOT LIKE '%test%')
8 - filter("R"."REGIMEN_TYPE_CODE"='CD')
9 - filter("RP"."REGIMEN_TYPE_CODE"='CD' AND "RP"."PRESCRIPTION_STATUS_CODE"='FNSH')
11 - access("A"."PERSON_NR"="RP"."PERSON_NR")
12 - access("A"."PERSON_NR"="B"."PERSON_NR")
13 - filter("B"."JOB_QUEUE_TYPE_NR"=4 AND "B"."STATUS_CODE"='FNSH')
14 - filter("A"."STATUS_CODE"='ACTV' OR "A"."STATUS_CODE"='CHNG')