Similar queries taking different times to complete

Question

I had the following query:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1,
    a, b, c, d, e
  FROM (
    SELECT
      a, b, c, d, e,
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1)) X
    FROM
      table
    WHERE
      a >= To_Date('&&1','YYYYMMDD')
      AND a < To_Date('&&1','YYYYMMDD')+1
  )
)
WHERE
  b LIKE ...
  AND e IS NULL
  AND adjust1>0
  AND (b NOT IN ('...','...','...'))
  OR  (b = '... AND c <> NULL)

I tried to change it to this:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1,
  FROM (
    SELECT
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1)) 
    FROM
      table
    WHERE
      a >= To_Date('&&1','YYYYMMDD')
      AND a < To_Date('&&1','YYYYMMDD')+1
      AND b LIKE '..'
      AND e IS NULL
      AND (b NOT IN ('..','..','..'))
      OR  (b='..' AND c <> NULL)
  )
)
WHERE
  adjust1>0

My intention is to have all the filtering in the innermost query, and only give the outer ones the field X which is the one I have to use a lot. However, the original query takes a couple of seconds to execute, while the second one won't even finish. I waited for almost 20 minutes and still no results.

Is there an obvious reason for this to happen that I might be overlooking?

Answer 1

These 2 queries are not the same and don't return the same result . You end up with following filters:

1st Query:

 WHERE
 (
 a >= To_Date('&&1','YYYYMMDD')
  AND a < To_Date('&&1','YYYYMMDD')+1
 )
 AND 
(
    b LIKE ...
   AND e IS NULL
   AND adjust1>0
   AND (b NOT IN ('...','...','...'))
   OR  (b = '... AND c <> NULL)
)

Second:

WHERE
  (
  a >= To_Date('&&1','YYYYMMDD')
  AND a < To_Date('&&1','YYYYMMDD')+1
  AND b LIKE '..'
  AND e IS NULL
  AND (b NOT IN ('..','..','..'))
  )
  OR  
 (b='..' AND c <> NULL)

You need to use parenthesis carefully while combining AND and OR predicates in one condition.