query tuning - Page 6
DBAsupport.com Forums - Powered by vBulletin
Page 6 of 6 FirstFirst ... 456
Results 51 to 54 of 54

Thread: query tuning

  1. #51
    Join Date
    Jan 2007
    Posts
    231
    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.,

  2. #52
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #53
    Join Date
    Jan 2007
    Posts
    231
    hrishy..,
    I tried that query.., it takes looong time.,
    I can use previous query itself.,

    hrishy cool down pls.,

  4. #54
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width