This discussion is archived
5 Replies Latest reply: Apr 2, 2013 3:12 AM by jeneesh RSS

Performance issue with the query

603438 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    603438 Newbie
    Currently Being Moderated
    This (+) outer join is not with join its with comparison condition.
  • 3. Re: Meaning of + in comparison condition
    jeneesh Guru
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Subject changed ..?

    Read FAQ: {message:id=9360003}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points