5 Replies Latest reply: Apr 2, 2013 5:12 AM by jeneesh RSS

    Performance issue with the query

    Nordik
      Hi All,

      select distinct /*+ PARALLEL(10) */
      P.EE_SSN "EMPLOYEE SSN",
      P.EMP_ID "EMPLOYEE ID",
      P.FNAME "EMPLOYEE FIRST NAME",
      P.LNAME "EMPLOYEE LAST NAME",
      P.DOB "EMPLOYEE BIRTH DATE",
      P.SEX "EMPLOYEE GENDER",
      p.STATUS "EMPLOYEE STATUS",
      P.CLASS "EMPLOYEE TYPE",
      P.ORG_CODE3 "FACILITY CODE",
      P.ORG_CODE2 "UNION CODE",
      P.ADDRESS1,
      P.ADDRESS2,
      P.CITY,
      P.STATE,
      P.ZIP "ZIP CODE",
      P.COUNTRY "COUNTRY CODE",
      P.PHONE,
      C.E_MAIL1 "EMAIL ADDRESS",
      P2.PARTIC_ID "PERSON NUMBER",
      P2.PARTIC_SSN "DEP SSN",
      P2.FNAME "DEP FIRST NAME",
      P2.LNAME "DEP LAST NAME",
      P2.DOB "DEP BIRTH DATE",
      P2.SEX "DEP GENDER",
      P2.RELATION,
      S.OPT_ID || '\' || S.COV_ID MEDICAL,
      S1.OPT_ID || '\' || S1.COV_ID DENTAL,
      decode(p2.special_status, 'H', 'H', ' ') "DEP DISABILITY STATUS",
      P2.STUDENT_IND "DEP STUDENT STATUS",
      P2.EXTRA_CODE10 "DEP AUDIT INDICATOR"
      FROM deo.V_PARTICIPANT P,
      deo.V_PARTICIPANT P2,
      deo.V_SELECTED S,
      deo.V_SELECTED S1,
      deo.HWC_USERS_CENTRAL C
      WHERE P.PLAN_ID = 'R113'
      AND P.PLAN_ID = P2.PLAN_id
      AND P2.PLAN_ID = S.PLAN_ID(+)
      AND P2.PLAN_ID = S1.PLAN_ID(+)
      AND P2.EE_PID = S.EE_PID(+)
      And p.ee_pid = p2.ee_pid
      AND P.EE_PID = C.EE_PID
      And p.relation = 'EE'
      And p2.relation not in ('XD', 'EE', 'XC', 'XP', 'XS')
      AND P2.PARTIC_ID = S.PARTIC_ID(+)
      AND P2.PARTIC_ID = S1.PARTIC_ID(+)
      AND (P2.EXTRA_CODE10 NOT LIKE 'V%' or P2.EXTRA_CODE10 is null)
      AND S.BEN_ID(+) IN('HO','HU')
      AND S1.BEN_ID(+) = 'HD'
      and s.cov_id(+) <> 'WV'
      and s1.cov_id(+) <> 'WV'
      and exists (select 'x'
      from deo.v_selected s2
      where s2.partic_id = p2.partic_id
      and s2.ben_id in ('HO','HU','HD')
      and s2.cov_id <> 'WV'
      and s2.plan_year = 13)
      Order by p.ee_ssn


      Thx
      Nordic

      Edited by: Nordik on Apr 2, 2013 3:07 AM
        • 1. Re: Meaning of + in comparison condition
          jeneesh
          It is an outer join.

          Suppose you have a, EMP_MASTER table and a CURRENT_PROJECT view.

          Join of these tables will give you employee details with current project.
          select e.emp_id,e.emp_naem,p.project_code
          from emp_master e, current_project p
          where e.emp_id = p.emp_id;
          This is a normal inner join. If there any employees without any project (ie, without an entry in CURRENT_PROJECT view) they will not appear in this query output.

          If you want to show all the employees here, you can use outer join. The project code will be shown as NULL for those who dont have an entry in the PROJECT view.
          select *
          from emp_master e, current_project p
          where e.emp_id = p.emp_id(+);
          You can use ANSI syntax also for the same
          select *
          from emp_master e left outer join current_project p
           on ( e.emp_id = p.emp_id )
          http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqlj29840.html#rrefsqlj29840

          Edited by: jeneesh on Apr 2, 2013 3:20 PM
          • 2. Re: Meaning of + in comparison condition
            Nordik
            This (+) outer join is not with join its with comparison condition.
            • 3. Re: Meaning of + in comparison condition
              jeneesh
              Nordik wrote:
              This (+) outer join is not with join its with comparison condition.
              Why cant you post the query?It can be any where, but it should be present in the join condition also - if it has to be meaningful..
              • 4. Re: Meaning of + in comparison condition
                _Karthick_
                An example with tables EMP and DEPT from SCOTT schema.

                Requirement - Get all the department name from DEPT table which has a maching record in EMP table where JOB is not MANAGER.
                SQL> select d.dname, e.*
                  from dept d, emp e
                 where e.deptno (+) = d.deptno
                   and e.job (+) != 'MANAGER';  2    3    4  
                 
                DNAME           EMPNO ENAME  JOB              MGR HIREDATE         SAL        COM     DEPTNO
                ---------- ---------- ------ --------- ---------- --------- ---------- ---------- ----------
                ACCOUNTING       7839 KING   PRESIDENT            17-NOV-81       5000          0         10
                RESEARCH         7369 SMITH  CLERK           7902 02-APR-81       2975          0         20
                RESEARCH         7788 SCOTT  ANALYST         7566 19-APR-87       3000          0         20
                RESEARCH         7876 ADAMS  CLERK           7788 23-MAY-87       1100          0         20
                SALES            7844 TURNER SALESMAN        7698 08-SEP-81       1500          0         30
                SALES            7521 WARD   SALESMAN        7698 22-FEB-81       1250        500         30
                SALES            7499 ALLEN  SALESMAN        7698 20-FEB-81       1600        300         30
                SALES            7654 MARTIN SALESMAN        7698 28-SEP-81       1250       1400         30
                OPERATIONS
                 
                9 rows selected.
                • 5. Re: Performance issue with the query
                  jeneesh
                  Subject changed ..?

                  Read FAQ: {message:id=9360003}