select count(ID) where ID IN a or b

Question

I don't understand what I'm doing wrong. I'm trying to get a weekly COUNT of every ID that meets criteria A OR criteria B.

  select CREATE_WEEK, count ( A.PK )
 from TABLE1 A
where    ( A.PK not in (select distinct ( B.FK )
                                    from TABLE2 B
                                  where B.CREATE_TIMESTAMP > '01-Jan-2013')
              or A.PK in (select A.PK
                                 from ( select A.PK, A.CREATE_TIMESTAMP as A_CRT, min ( B.CREATE_TIMESTAMP ) as FIRST_B
                                              from TABLE1 A, TABLE2 B
                                             where    A.PK = B.FK
                                                     and A.CREATE_TIMESTAMP > '01-Jan-2013'
                                                     and B.CREATE_TIMESTAMP > '01-Jan-2013'
                                         group by A.PK, A.CREATE_TIMESTAMP)
                                where A_CRT < FIRST_B) )
        and A.CREATE_TIMESTAMP > '01-Jan-2013'
        and CREATE_WEEK >= 2
        and THIS_WEEK - CREATE_WEEK >= 1
group by CREATE_WEEK 
order by CREATE_WEEK asc

**Note: PK in table1 = FK in table2, so in the first subquery, I'm checking whether the PK from table1 exists as FK in table2. Week comes from TO_CHAR (TO_DATE (TRUNC (A.CREATE_TIMESTAMP, 'IW')), 'IW')

When I take out the OR and run the query on either subquery the results are returned in 1-2 seconds. But when I try to run the combined query, the results aren't returned after 20 minutes.

I know I can run them separately and then sum them in a spreadsheet, but I'd rather just get one number.

Answer 1

I'm trying to get a weekly COUNT of every ID that meets criteria A OR criteria B

However your code is:

ID NOT IN (subquery A) OR ID IN (subquery B)

The NOT is at odds with your requirement.

Assuming you ID's that meet both criteria, use:

ID in (
  select ... -- this is subquery A
  union
  select ... -- this is subquery B)