4 Replies Latest reply: Dec 20, 2012 10:03 AM by ora001 RSS

    Multiple Exists in sub query

    755501
      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
          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
            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
              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
                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