SQL query for adding column value to compare with other column

I assume that we can additionally GROUP BY inventory_item_key without changing the result. If we can further assume that there are no (or few) rows in table_inventory without a matching row in table_item, then this query should be a bit faster:

SELECT l.node_key
      ,l.rack_id
      ,i.item_id
      ,net_qty
FROM   table_item i
JOIN  (
    SELECT inventory_item_key, node_key, rack_id 
          ,SUM(quantity + in_coming_qty - locked_qty) AS net_qty 
    FROM   table_inventory
    GROUP  BY inventory_item_key, node_key, rack_id
    HAVING SUM(quantity + in_coming_qty - locked_qty) AS net_qty < 0
    ) l USING (inventory_item_key)

Major points:

• By aggregating and filtering the values table_inventory before the JOIN, fewer join-operations have to be made.

• I simplified your quantity aggregation to SUM(quantity + in_coming_qty - locked_qty). the other possibility would be SUM(quantity) + SUM(in_coming_qty) - SUM(locked_qty). Either way makes more sense that the mixture you had. You would have to test which is fastest.

• An explicit JOIN clause is preferable in SQL if applicable. In this case you can further simplify the syntax to USING (inventory_item_key).

The index @Chris proposes is impossible. Oracle has no way of knowing how to group these values. You need an index on table_item.inventory_item_key. Other than that I am not sure how to index aggregated values.

If speed is imperative and the values in table_inventory do not change very often, you could create a materialized view out of the sub-query l or the whole query. This would be a decisive speed-up.

I am not exactly familiar with Oracle syntax, but I know that Oracle supports indexes on functions. I'm not sure if it will be used, since there is grouping involved, but you may wish to look into creating an index for this query if performance of the query becomes an issue. A guess at the syntax used to create the index would be:

Create index net_qty on table_inventory(SUM(quantity + in_coming_qty) - SUM(locked_qty));

You can search for "Oracle function based index" for more information.