current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite

I have two tables

table_inventory
    List item
    inventory_rack_key(primarykey)
    node_key
    rack_id
    inventory_item_key
    in_coming_qty,locked_qty
    quantity

table_item
    inventory_item_key(primary key)
    item_id,product_zone

The table example are provided here DB TABLES
I need query to find out those items for which (net_qty) i.e difference b/w sum of in_coming_qty & quantity & locked_qty is negative. arranged by node_key,rack_id, item_id,net_qty

Note: each distinct set {node_key,rack_id, item_id,net_qty} will have only 1 row in output. For ex :{node_key,rack_id, item_id} = {ABD101,RK-01,562879} has 4 rows in table_inventory but in output net_qty= -78(single row) .

The query I made is giving me result but can we do it in some other way?

SELECT l.node_key, 
       l.rack_id, 
       i.item_id, 
       ( SUM(l.quantity + l.in_coming_qty) - SUM(l.locked_qty) ) AS net_qty 
FROM   table_inventory l, 
       table_item i 
WHERE  l.inventory_item_key = i.inventory_item_key 
GROUP  BY l.node_key, 
          l.rack_id, 
          i.item_id 
HAVING SUM(l.quantity + l.in_coming_qty) - SUM(l.locked_qty) < 0
share|improve this question
    
Which database engine are you using? MSSQL, Oracle, etc? – Chris Marasti-Georg Dec 28 '11 at 13:25
    
@ChrisMarasti-Georg Oracle SQL Developer – livinggourmand Dec 28 '11 at 13:57
up vote 1 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer

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.