Need halp analyzing the sql in a view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Need halp analyzing the sql in a view

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    Need halp analyzing the sql in a view

    I am a new DBA and have a developer who created a view in our HrMS application. When he quieries the view it processes forever and never returns any results. He gave me the project of fixing his view. When I look at it the sql in there has so many where clauses with nested select statements. I ran the index tunning wizzard and implemented it's suggestions but it didn't sugest anything for his view. I wanted to plug his code in the view into SQL analyzer but my company has standard edition and I don't see it installed. I was wondering if any one could help me out. Here is the script for his view.
    SELECT A.ROLEUSER , A.DESCR , A.OPRID , A.EMAILID , A.FORMID , A.WORKLIST_USER_SW , A.EMAIL_USER_SW , A.FORMS_USER_SW , A.EMPLID , A.ROLEUSER_ALT , A.ROLEUSER_SUPR , A.EFFDT_FROM , A.EFFDT_TO , B.ROWSECCLASS , ' ' FROM PS_ROLEXLATOPR A , PSOPRDEFN B WHERE
    A.EMPLID = B.EMPLID AND A.OPRID = B.OPRID AND B.ROWSECCLASS NOT IN ('HCDPALL','HCDP_ALT_EE','HCDPNJ01','HCDPMA01','EPEX9000',' ','HCDP_ALT11_SLSFLD','HCDP_ALT10_ALL','HCDP_ALT11_ALL','HCDP_ALT12_ALL') AND B.EMAILID <> ' ' AND B.EMAILID <> 'PSReports@AltanaPharma-US.com' AND B.EMAILID <> 'gonowhere@altanainc.com' AND B.OPRID NOT IN ( SELECT C.OPRID FROM PS_ROLEUSER_VW C WHERE C.OPRID = B.OPRID AND ( C.ROLENAME = 'ALT_WKFLOW_HIRE_TERM' OR C.ROLENAME = 'ALT_WKFLOW_XPAY' ) ) UNION SELECT A1.ROLEUSER , A1.DESCR , A1.OPRID , A1.EMAILID , A1.FORMID , A1.WORKLIST_USER_SW , A1.EMAIL_USER_SW , A1.FORMS_USER_SW , A1.EMPLID , A1.ROLEUSER_ALT , A1.ROLEUSER_SUPR , A1.EFFDT_FROM , A1.EFFDT_TO , B1.ROWSECCLASS , C1.EMPLID FROM PS_ROLEXLATOPR A1 , PSOPRDEFN B1 , PS_EMPL_CURRJOB_VW C1 WHERE A1.EMPLID = B1.EMPLID AND A1.OPRID = B1.OPRID AND A1.EMPLID = C1.SUPERVISOR_ID AND B1.ROWSECCLASS = 'HCDP_ALT_EE' AND B1.EMAILID <> ' ' AND B1.EMAILID <> 'PSReports@AltanaPharma-US.com'

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    repost it using the code tags and paragraphs, it's impossible to read like that

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    I am not sure what you mean by code tags but does this format help.
    Thanks

    SELECT A.ROLEUSER
    , A.DESCR
    , A.OPRID
    , A.EMAILID
    , A.FORMID
    , A.WORKLIST_USER_SW
    , A.EMAIL_USER_SW
    , A.FORMS_USER_SW
    , A.EMPLID
    , A.ROLEUSER_ALT
    , A.ROLEUSER_SUPR
    , A.EFFDT_FROM
    , A.EFFDT_TO
    , B.ROWSECCLASS
    , ' '
    FROM PS_ROLEXLATOPR A
    , PSOPRDEFN B
    WHERE A.EMPLID = B.EMPLID
    AND A.OPRID = B.OPRID
    AND B.ROWSECCLASS NOT IN ('HCDPALL','HCDP_ALT_EE','HCDPNJ01','HCDPMA01','EPEX9000',' ','HCDP_ALT11_SLSFLD','HCDP_ALT10_ALL','HCDP_ALT11_ALL','HCDP_ALT12_ALL')
    AND B.EMAILID <> ' '
    AND B.EMAILID <> 'PSReports@AltanaPharma-US.com'
    AND B.EMAILID <> 'gonowhere@altanainc.com'
    AND B.OPRID NOT IN (
    SELECT C.OPRID
    FROM PS_ROLEUSER_VW C
    WHERE C.OPRID = B.OPRID
    AND ( C.ROLENAME = 'ALT_WKFLOW_HIRE_TERM'
    OR C.ROLENAME = 'ALT_WKFLOW_XPAY' ) )
    UNION
    SELECT A1.ROLEUSER
    , A1.DESCR
    , A1.OPRID
    , A1.EMAILID
    , A1.FORMID
    , A1.WORKLIST_USER_SW
    , A1.EMAIL_USER_SW
    , A1.FORMS_USER_SW
    , A1.EMPLID
    , A1.ROLEUSER_ALT
    , A1.ROLEUSER_SUPR
    , A1.EFFDT_FROM
    , A1.EFFDT_TO
    , B1.ROWSECCLASS
    , C1.EMPLID
    FROM PS_ROLEXLATOPR A1
    , PSOPRDEFN B1
    , PS_EMPL_CURRJOB_VW C1
    WHERE A1.EMPLID = B1.EMPLID
    AND A1.OPRID = B1.OPRID
    AND A1.EMPLID = C1.SUPERVISOR_ID
    AND B1.ROWSECCLASS = 'HCDP_ALT_EE'
    AND B1.EMAILID <> ' '
    AND B1.EMAILID <> 'PSReports@AltanaPharma-US.com'

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Sure you need the UNION, not a UNION ALL?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2005
    Posts
    143
    The union all made it alot faster. I don't know why, but thank you.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Because UNION is an implicit DISTINCT on the result set
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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