query tuning

Code:

---

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.,

Quote:

---

Originally Posted by hrishy

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

Try This as Abhay Suggested

Code:

---

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

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

hrishy cool down pls.,

Quote:

---

Originally Posted by hrishy

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.