query tuning

[ams-jamali]

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 - access("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("A"."STATUS_CODE"='ACTV' OR "A"."STATUS_CODE"='CHNG')
  14 - filter("B"."JOB_QUEUE_TYPE_NR"=4 AND "B"."STATUS_CODE"='FNSH')

sorry for the delay.,

[abhaysk]

Hi
And Abhay is right you have used some regimen_pres table twice

Try This as Abhay Suggested

SELECT
        rp.person_nr,
        FIRST_VALUE(rp.regimen_sequence) 
 		OVER (PARTITION BY rp.person_nr, rp.regimen_type_code
                         order by rp.regimen_sequence desc ) regimen_sequence,
        rp.regimen_type_code 
FROM    REGIMEN_PRES rp,
        PERSON p 
WHERE  
 	rp.person_nr = p.person_nr 
    AND rp.regimen_type_code = 'CD' 
    AND rp.prescription_status_code= 'FNSH' 
    and p.email not like '%test%' 
    AND p.person_nr IN     (SELECT  b.person_nr 
                            FROM    JOB_QUEUE b
                            WHERE   
				AND b.job_queue_type_nr = 4 
                                AND b.status_code = 'FNSH'
                                AND (a.status_code = 'ACTV' or 
                                     a.status_code = 'CHNG')) 
ORDER BY rp.person_nr

regards
Hrishy

Hrishy,

A distinct is needed as was in my post, else he will get redundant data.


Rgds
Abhay

[ams-jamali]

hrishy..,
I tried that query.., it takes looong time.,
I can use previous query itself.,

hrishy cool down pls.,

[PAVB]

At this rate i will loose my patience and might switch to a SYBASE forum

LMAO... I feel your pain Hrishy, this particular thread is totally out of control, pure insanity.