This discussion is archived
4 Replies Latest reply: Dec 20, 2012 8:03 AM by ora001 RSS

Multiple Exists in sub query

755501 Newbie
Currently Being Moderated
I am writing a SQL statement that has multiple EXISTS in its sub-select (see below). I want the records to be returned if one or the other EXISTS returns. For performance, is it best to surround the 'EXISTS' with an 'OR'? Is there a better way to do it?

SELECT emp.empid from emp
WHERE
EXISTS(SELECT ''
FROM meets, stu_schedule, stu_school, stu_year, sis_user
WHERE sis_user.user_name = v('APP_USER')
and stu_year.fk_stu_base = stu_base.pk_id
and stu_school.fk_stu_year = stu_year.pk_id
and stu_schedule.fk_stu_school = stu_school.pk_id
and stu_schedule.fk_meets = meets.pk_id
and sis_user.fk_staff = meets.fk_staff)


EXISTS(SELECT ''
FROM staff_assignment, homeroom, stu_school, stu_year, sis_user
WHERE sis_user.user_name = v('APP_USER')
and stu_year.fk_stu_base = stu_base.pk_id
and stu_school.fk_stu_year = stu_year.pk_id
and stu_school.fk_homeroom = homeroom.pk_id
and staff_assignment.fk_homeroom = homeroom.pk_id
and sis_user.fk_staff = staff_assignment.fk_staff)
  • 1. Re: Multiple Exists in sub query
    Mark D Powell Guru
    Currently Being Moderated
    Yes you can use an OR condition between the two EXISTS clauses. Oracle will not execute the second EXISTS when the first gets a hit.

    If there might be a better way to write the query depends on the object structure, that is, what indexes you have defined, and what percentage of the employees will meet one condition or the other.

    If you really want someone to look at the query you should post DDL for all tables with inserts of sample data but considering the number of tables this is probably not practical.

    You could explain exactly what you are looking for, the relative data quantity, and some other facts and ask for approaches to check if query performance seems to be an issue.

    HTH -- Mark D Powell --
  • 2. Re: Multiple Exists in sub query
    ora001 Newbie
    Currently Being Moderated
    Use UNION ALL to replace OR with multiple EXISTS. In the case Oracle will exacute the sub-query and there is only one EXISTS to evaluate.
  • 3. Re: Multiple Exists in sub query
    Purvesh K Guru
    Currently Being Moderated
    bobmagan wrote:
    For performance, is it best to surround the 'EXISTS' with an 'OR'? Is there a better way to do it?
    A misconception, I would say so.

    AND and OR operators are conditional operators and do not have any performance constraints associated with them. They are to be used per se your business requirement.
    Below test case should elaborate, what I mean
    create table test_table(col1 varchar2(1), col2 number);
    
    insert into test_table values ('A', 1);
    insert into test_table values ('B', 2);
    
    select col1
      from test_table
     where col2 = 1 or col2 = 2;
    
    COL1 
    ---- 
    A    
    B
    To answer your original question, It is possible to use Multiple Exists in your query, provided those have an appropriate Conditional Operator amongst them.
  • 4. Re: Multiple Exists in sub query
    ora001 Newbie
    Currently Being Moderated
    Question to Mark D Powell on his response line "...what indexes you have defined....". In the example below unless "*sr.supplysource*" has stand alone index, the query does not seems executing the third EXIST clause.

    Why does Oracle decide whether to execute the clause base on index?

    I know UNION ALL is a workaround, but curious to learn why INDEX makes difference in the context?


    ....
    WHERE
    EXISTS( SELECT 1 FROM stsc.inventory inventory
    WHERE t.item = inventory.item
    AND t.fromloc = inventory1.loc
    AND inventory.qty => t.stqty
    AND sr.supplysource = 1 )
    OR EXISTS( SELECT 1 FROM stsc.inventory inventory
    WHERE t.item = inventory.item
    AND t.fromloc = inventory2.loc
    AND inventory.qty => t.stqty
    AND sr.supplysource = 2 )
    OR EXISTS( SELECT 1 FROM stsc.inventory inventory
    WHERE t.item = inventory.item
    AND t.fromloc = inventory3.loc
    AND inventory.qty => t.stqty
    AND sr.supplysource =3 )

    Thanks,
    Prakash

Legend

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