Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.