Search code examples
oracleviewnot-exists

Oracle view in NOT EXISTS query not working

I have the following query that is not working. It returns all the rows from inv view. When I copied the view to a static table and use the static table in the query, the query works as it should and only results in a few rows. So something is wrong with using a view with a NOT EXISTS subquery. Can someone explain if this is true or if I have another error.

Update: In our dev environment, the exact same query is working fine. Now I am even more confused.

SELECT inv.organization_id, inv.inventory_item_id
  FROM inventory_v inv
 WHERE NOT EXISTS (SELECT 1 
                     FROM inventory_int xx
                    WHERE xx.item_id = inv.inventory_item_id 
                      AND xx.org_id = inv.organization_id
                  )
;

Update: See explain plan for dev query that works, and prod query that does not work. I don't really know how to interpret it.

Query that works

Query that DOES NOT work

Update: Still can't figure it out. Weird that the production explain plan does not have the filter, but the dev one does. Production db is 12c, dev db is 19c.

PROD

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("INVENTORY_ITEM_ID"(+)="MSI"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"(+)="MSI"."ORGANIZATION_ID")
   5 - access("ORG"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID")
   7 - access("SUB"."SUBINVENTORY_CODE"="OH"."SUBINVENTORY_CODE" AND "SUB"."PRIMARY_SHIPPING_ORG_ID"="OH"."ORGANIZATION_ID")
   9 - access("MSI"."ORGANIZATION_ID"="PRIMARY_SHIPPING_ORG_ID")
  11 - access("ERP_ORG_ID"=TO_NUMBER("CONTROL_VALUE"))
  12 - filter("EXTRACT_TYPE"='ORG' AND "ACTIVE_FLAG"='Y')
  13 - access("CONTROL_TYPE"='SFC_E_EBS_OPERATING_UNIT')
  15 - access("OH"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID" AND "OH"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID")

DEV

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter( NOT EXISTS (SELECT 0 FROM "SFC_E_INVENTORY_INT" "XX" WHERE "XX"."ITEM_ID"=:B1 AND "XX"."ORG_ID"=:B2))
   3 - access("INVENTORY_ITEM_ID"(+)="MSI"."INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"(+)="MSI"."ORGANIZATION_ID")
   6 - access("ORG"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID")
   8 - access("SUB"."SUBINVENTORY_CODE"="OH"."SUBINVENTORY_CODE" AND "SUB"."PRIMARY_SHIPPING_ORG_ID"="OH"."ORGANIZATION_ID")
  10 - access("MSI"."ORGANIZATION_ID"="PRIMARY_SHIPPING_ORG_ID")
  12 - access("ERP_ORG_ID"=TO_NUMBER("CONTROL_VALUE"))
  13 - filter("EXTRACT_TYPE"='ORG' AND "ACTIVE_FLAG"='Y')
  14 - access("CONTROL_TYPE"='SFC_E_EBS_OPERATING_UNIT')
  16 - access("OH"."ORGANIZATION_ID"="MSI"."ORGANIZATION_ID" AND "OH"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID")
  19 - access("XX"."ORG_ID"=:B1 AND "XX"."ITEM_ID"=:B2)
Solution

  • It turns out the view was referencing a table over a dblink to another Oracle instance. I think maybe I encountered a bug in 12c, because it works fine in 19c. I moved the NOT EXISTS inside the view, and that solved the problem for me.