Oracle sql with subqueries causing peformance issue

Question

Writing oracle sql with 3 subqueries, however it is causing a timeout issue. Hoping for technical help on getting to the fix. Here is my query:

select   DISTINCT E_reg.applicant_id,
             E_REG.L_NAME, 
             E_REG.F_NAME, 
             E_REG.B_DATE,
            E_REG.REG_DATE,
 from     E_REG,TRANSACTION
where   E_REG.ID=TRANSACTION.PAR_ID
  and       TRANSACTION.BEGIN_DATE BETWEEN to_date ('01-APR-2012')AND to_date('30-JUN-               2012')
and  e_reg.applicant_id NOT IN
              (select applicant_id
                   from w_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date >='01-APR-2012'
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from t_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date>='01-APR-2012')
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from r_reg
                  where reg_date <'01-JUL-2012' 
                     and o_attend IS NOT NULL
                   and term_date is NULL or term_date >='01-APR-2012')

Basically we have 4 programs that you can be in ( e, w, t, and r). Those are all seperate tables that house basic participant information. You may be in all 4 programs at the same time, the applicant_id will be the key for everyone.

The transaction table house any service you may receive in that 3 or the 4 programs, not the r program, that has its own transaction table.

I want a listing of participants in e table that had a service in the timeframe but did not have any services in any of the other programs at the same time. They would be only serviced though the e program. It seemed to work this morning but then it starting timing out and not running at all. I figure it must be the subquery. Wondering if there is a better way to make this work.

and the explain plan for this looks like what? If you can provide a SQL fiddle people will be more inclined to help. — Woot4Moo
– Woot4Moo, Commented May 10, 2013 at 18:38
This looks like it is doing full table scans every time, which may be a bottle neck. — Woot4Moo
– Woot4Moo, Commented May 10, 2013 at 18:40
please post query execution plan and table definitions with indexes and preferably with row count estimates for each table — Kirill Leontev
– Kirill Leontev, Commented May 10, 2013 at 18:45

Gordon Linoff · Accepted Answer · 2013-05-10 18:52:24Z

1

I'm pretty sure the problem is parentheses in your where clause. You have or clauses that are undoing the join. If you always use ANSI join syntax, you greatly reduce the possibility of this occurring.

Here is the alternative version:

select   DISTINCT E_reg.applicant_id,
             E_REG.L_NAME, 
             E_REG.F_NAME, 
             E_REG.B_DATE,
            E_REG.REG_DATE,
 from    E_REG join
         TRANSACTION
         on E_REG.ID=TRANSACTION.PAR_ID
  where TRANSACTION.BEGIN_DATE BETWEEN to_date ('01-APR-2012')AND to_date('30-JUN-               2012')
and  (e_reg.applicant_id NOT IN
              (select applicant_id
                   from w_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date >='01-APR-2012'
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from t_reg
                  where reg_date <'01-JUL-2012' 
                   and exit_date is NULL or exit_date>='01-APR-2012')
or  e_reg.applicant_id NOT IN
              (select applicant_id
                   from r_reg
                  where reg_date <'01-JUL-2012' 
                     and o_attend IS NOT NULL
                   and term_date is NULL or term_date >='01-APR-2012')
   )

answered May 10, 2013 at 18:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1466935 Over a year ago

I took out the "or" and replaced with "and" and adjusted the join syntax. Thanks!

Collectives™ on Stack Overflow

Oracle sql with subqueries causing peformance issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related