CODEWITHSUNDEEP
oracle-database
Alex
Alex

Reputation: 315

Similar queries have way different execution times

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)) X
    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

Mi intention was to have all the filtering in the innermost query, and only give to the outer ones the field X which is the one I have to operate a lot. However, the firts (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 I wouldn't get the answer.

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

These are the plans for each of them:

SELECT STATEMENT optimizer=all_rows (cost = 973 Card = 1 bytes = 288)
  SORT (aggregate)
    PARTITION RANGE (single) (cost=973 Card = 3 bytes = 864)
      TABLE ACCESS (full) OF "table" #3 TABLE Optimizer = analyzed(cost=973 Card = 3 bytes=564)


SELECT STATEMENT optimizer=all_rows (cost = 750.354 Card = 1 bytes = 288)
  SORT (aggregate)
    PARTITION RANGE (ALL) (cost=759.354 Cart = 64.339 bytes = 18.529.632)
      TABLE ACCESS (full) OF "table" #3 TABLE Optimizer = analyzed(cost=750.354 Card = 64.339 bytes=18.529.632)

Upvotes: 1

Views: 118

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Your two queries are not identical.

the logical operator AND is evaluated before the operator OR:

SQL> WITH data AS
  2          (SELECT rownum id
  3             FROM dual
  4           CONNECT BY level <= 10)
  5  SELECT *
  6    FROM data
  7   WHERE id = 2
  8     AND id = 3
  9      OR  id = 5;

        ID
----------
         5

So your first query means: Give me the big SUM over this partition when the data is this way.

Your second query means: give me the big SUM over (this partition when the data is this way) or (when the data is this other way [no partition elimination hence big full scan])

Be careful when mixing the logical operators AND and OR. My advice would be to use brackets so as to avoid any confusion.

Upvotes: 3

Craig
Craig

Reputation: 5820

It is all about your OR... Try this:

SELECT nvl(sum(adjust1),0)
FROM (
  SELECT
    ManyOperationsOnFieldX adjust1
  FROM (
    SELECT
      SubStr(balance, INSTR(balance, '[&&2~', 1, 1)) X
    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

Because you have the OR inline with the rest of your AND statements with no parenthesis, the 2nd version isn't limiting the data checked to just the rows that fall in the date filter. For more info, see the documentation of Condition Precedence

Upvotes: 2

Related Questions