0

Yesterday the new oracle server was installed. DB was migrated to exadata. Query that before was working fine suddenly started to show different results for the first run and for subsequent runs.

SELECT CASE
         WHEN MAX(recommended_unit_retail) != MIN(recommended_unit_retail) THEN
          item
         ELSE
          '-1'
       END item
      ,MIN(recommended_unit_retail) min_ur
      ,MAX(recommended_unit_retail) max_ur
  FROM (SELECT item
              ,loc
              ,recommended_unit_retail
              ,action_date
          FROM (SELECT rps.item
                      ,rps.loc
                      ,rps.recommended_unit_retail
                      ,rps.action_date                      
                      ,row_number() over(PARTITION BY rps.item, rps.loc ORDER BY rps.action_date DESC
                        ) rn
                  FROM uda_item_lov uil
                  JOIN xxlm_recom_prc_storage rps
                    ON rps.item = uil.item
                   AND rps.status IN ('S', 'P')
                   AND rps.action_date <= trunc(SYSDATE + 1)
                  JOIN store s
                    ON s.store = rps.loc
                   AND s.district = 1
                   AND s.store_open_date IS NOT NULL
                   AND s.store_open_date <= trunc(SYSDATE)
                  JOIN item_loc il
                    ON il.item = rps.item
                   AND il.loc = rps.loc
                   AND il.status = 'A'
                 WHERE uil.uda_id = 5
                   AND uil.uda_value != 6
                   AND uil.item = ANY(82584520, 82142283, 82377842))
         WHERE rn = 1)
 GROUP BY item;

The data on the first run is

---------------------
-1 | 548  | 548
-1 | 1708 | 1708
-1 | 1926 | 1926
---------------------

For the second and subsequent runs it's

----------------------------
82584520 | 548  | 710
82142283 | 1708 | 1926
82377842 | 1926 | 1937
----------------------------

The plan for all queries is the same. What could be the problem?

P.S. I've thought it could be related to the different date NLS settings, but we checked and it's the same as it was on the old server.

kinderproc
  • 198
  • 3
  • 9
  • What is the version of the database, to 4 decimals. I saw something like this several years ago on an early 11.2 database. There was a patch, but I can't remember the details. Might be worth opening an SR with oracle support, as painful as that can be. – EdStevens Feb 16 '21 at 20:59
  • 3
    If the items in the row_number analytic PARTITION / ORDER BY aren't unique, then the different rows could be different orderings depending on the query plan and the rn=1 could be 'picking any of the rows with that item, loc and action_date' – Gary Myers Feb 16 '21 at 21:33
  • As @GaryMyers says, there could be mulitple rows with the same `action_date` for every `rps.item`, `rps.loc` combination. In those cases the engine is free to sort the rows in any way it wants... and that order may change every time you run the query. – The Impaler Feb 16 '21 at 21:40
  • 1
    Just a thought but you are aliasing your first, calculated, column to the same name as an existing column "item" and then grouping by it - so there may be some confusion/inconsistency there. I would use some other alias name and see if that makes a difference – NickW Feb 16 '21 at 23:07
  • @NickW Nice idea! Unfortunately it didn't help. I've changed the name for calculated column to item_value. The problem is still there. – kinderproc Feb 17 '21 at 09:12
  • @GaryMyers thanks for explanation! For sure it's the case. You helped us to reach the root of the problem. At the end we found that data is wrong due to datafix applied few days ago. – kinderproc Feb 17 '21 at 09:18

1 Answers1

0

As Gary Myers said in comments, the problem appears when "the items in the row_number analytic PARTITION / ORDER BY aren't unique, then the different rows could be different orderings depending on the query plan and the rn=1 could be 'picking any of the rows with that item, loc and action_date". According to our case, the problem is that data is wrong.

kinderproc
  • 198
  • 3
  • 9