Systems Engineering and RDBMS

Archive for April 30th, 2008

A simple inventory query

Posted by decipherinfosys on April 30, 2008

This is one of the common requirements so we thought we would put this out there on the blog as a solution.  At one of our clients, they had running inventory list of the items in their warehouse.  This inventory list was stored in a table which for the sake of brevity had these attributes that we were interested in:

ITEM_NBR        VARCHAR2(20),
ITEM_QTY        NUMBER(10),
BIN_NBR            VARCHAR2(10),

And here is a sample of how the data looked like:

item_nbr item_qty bin_nbr date_created
——– ——– ——- ————-
Item_1         10  D1        02/01/2008
Item_1         20  D2        02/01/2008
Item_1        100  D1        04/01/2008
Item_1         50  D2        04/02/2008
Item_1         30  D3        04/03/2008
Item_1         20  D8        04/04/2008
Item_2         10  D1        02/01/2008
Item_2         20  D2        02/01/2008
Item_2         77  D1        04/01/2008
Item_2         32  D2        04/02/2008
Item_2         52  D3        04/03/2008
Item_2         33  D8        04/04/2008

Whenever a new record was created in this table, the running total of the item’s quantity was input into the item_qty column based on an item and the bin_nbr that it was placed in.  Example: If Item_1 is present in bins D1, D2, D3 and D8, when a new record got created on say April 1st 2008 for item_1 in bin D1, the new 80 quantity got added to the existing 20 in that bin for that item and the record was created with 100.

What the report was supposed to show was the total quantity as of any given run for an item regardless of the bin numbers.

There are a lot of ways to do this.  We will show some of the approaches.

Approach #1: This approach uses the analytic function first_value() in Oracle.  Using that function and by ordering on the date_created in a desc fashion and by partitioning on the item_nbr and bin_nbr, one can select the max value for the item per bin and then apply the grouping at the top of that.

SQL> select item_nbr, sum(item_qty)
2    from (
3  select distinct item_nbr, bin_nbr,
4         first_value(item_qty) over(partition by item_nbr, bin_nbr
5                                       order by date_created desc) item_qty
6    from invn_item
7    )
8   group by item_nbr
9  /

——– ———-
Item_1        200
Item_2        194

Approach #2: This will work in Oracle as well as SQL Server:  This approach uses the MAX() function and a co-related sub-query:

SQL> select item_nbr, sum(item_qty)
2    from invn_item
3   where date_created = ( select max(date_created)
4                              from invn_item it2
5                             where it2.item_nbr = invn_item.item_nbr
6                               and it2.bin_nbr = invn_item.bin_nbr )
7   group by item_nbr
8  /

——– ———-
Item_1        200
Item_2        194

Another approach is to use the row_number() function (this is also available in SQL Server 2005) and partition on the item_nbr and bin_nbr and order by date_created desc and then pick up the first record in the outer query and do a grouping on it.  It will prevent the usage of the DISTINCT clause as well that was used in Approach #1 above.

Posted in Oracle, SQL Server | Leave a Comment »

Latches, Locks, Enqueues and Semaphores

Posted by decipherinfosys on April 30, 2008

A developer at a client site asked this question yesterday – “I know what is the difference between latches and locks but what are enqueues and semaphores – I have heard those terms used by the DBAs a lot…could you explain those for me?”.

Our answer was that latches and enqueues are both types of locks where as a semaphore is an OS supplied serialization device which a programmer uses to implement latching/enqueuing.  Latches are essentially lightweight serialization devices.  They are similar to spinlocks that spins inside a loop for the bit to be cleared up by another process – it is a mutex (mutual exclusion device).  Latches are used to serialize access to memory data structures like the SGA data structures.  Enqueues on the other hand are heavyweight serialization devices.  Enqueues are used to perform row level locking.  Unlike latches, these are actual locks on the data records.

Posted in Oracle | Leave a Comment »