Alternatives to using a job?
January 17th, 2006 By timboc
I’m looking for alternative approaches to the following issue.
We currently have an inventory application that tracks the receipt (number of items in inventory goes up) and issue (number of items in inventory goes down) of products.
I’ve currently implemented the task of knowing how many of a particular product is “In Stock†by placing a trigger on the transactional tables and inserting a row to represent the increment, or decrement, of a product into a special table. There is a job running every 60 seconds to look for rows in this special table and maintain the master “In Stock†table. Since there is only 1 instance of this job there is never any issue with two processes modifying the “In Stock†values incorrectly. Also, the transactional processes that create the increment/decrement rows only make these rows accessible to the job by committing their data.
A few downsides to this technique:
1. It can take up to 60 seconds for the correct “In Stock†quantity of an item to appear.
2. If the job breaks (or if the DBA sets job_queue_processes to 0) then “In Stock†quantities never update as the increment/decrement rows are stuck in the special table.
Does anyone have alternative ideas as to how I can attack this problem?
Any help would be greatly appreciated.
-Tim
January 17th, 2006 at 12:44 pm
Hi,
As this is a ’special’ table, could a materialized view with refresh on commit do the job?
regards
Jan
January 17th, 2006 at 12:49 pm
Hi Tim,
This is not the type of question I would ask on OraQA - At least this is what I had in mind when I created OraQA.
In the About page it says: both questions and answers should be specific, simple and atomic. You are asking more like a design question which does not fall into these categories.
Plus, answers to design questions may work in one business environment but not in another.
However, I did see in your question a little bit more generic issue like how to handle transactions and concurrency in an OLTP system for example.
January 17th, 2006 at 2:29 pm
Would a Materialized view help?
just do a refresh on commit.
January 18th, 2006 at 1:17 am
I don’t know how frequently your transactions update, nor do I know if you are using a DB link to transfer into a different database. I assume it is all the same DB since the register or client updates the transaction through the same DB connection that it checks inventory.
You don’t even need a materialized view, but you could do that method without the triggers I suppose.
What you should do is just a view based on the special table that holds the increment / decrement number. Does this table just have increment and decrement? Has soon as inventory arrives in the warehouse is this table incremented/decremented approproately?
Maybe you could index the table and put a view on it. Group by Product and inventoy. With the inventory or in-stock field just being a SUM of the increment/decrement number.
Example :
CREATE OR REPLACE VIEW in_stock_items
AS
SELECT item_no, SUM(inc_dec)
FROM special_table
GROUP BY item_no;
It’s really simple, but ONLY YOU know your database and how it is configured, and it should be you know would know the pros and cons of doing certain operations. But based on what you tell me, this is probably how I would set it up.
If you had a lot of updates, then you may wat to reconsider adding an index on this table. You will want to avoid full table scans if it gets too large. If thst is the case, then maybe you could do partitioned tables.
Again, this is design and there is no one right answer, you can solve this in many different ways.
Good Luck to you!
January 18th, 2006 at 1:21 am
Oops, a couple typs as it’s late.
CREATE OR REPLACE VIEW in_stock_items
AS
SELECT item_no, SUM(inc_dec) AS inventory
FROM special_table
GROUP BY item_no;
Then in your application, just have it query that view.
SELECT inventory
FROM special_table
WHERE item_no = pass in paramter.
You could also right a procedure when a character/number in (depending on is it’s numbers only or not) and have an output of number that spits out inventory.
That way you could just exec or call the procedure and pass in just the item_number.
January 18th, 2006 at 1:22 am
Still typos, geeze.
I’m sorry; I should really be proofreading.
January 18th, 2006 at 5:12 am
The issue with a materialized view is that there are too many transactions being posted to this table to make it useful (in my honest opinion).
I’ll further my original post with more details to hopefully make it clear what I’m doing.
1. My “in stock” table is named t_inventory (product_id, in_stock)
2. I can see the current “in stock” by selecting a single row from this table by using it’s product_id.
3. t_inventory has an index on product_id
4. When a transaction that affects “in stock” is committed I place a row in t_inventory_queue (product_id, in_stock_change), which contains the product_id and the amount to increment or decrement “in stock”
5. I have a job in my database that runs every minute and takes the changes from t_inventory_queue and updates (or inserts) rows in t_inventory, it then deletes the row from t_inventory_queue.
The benefit of the t_inventory approach (instead of doing a select sum() with a group by) is that in much of my application I can include t_inventory right in my SQL FROM clauses, instead of having to do inline views or call stored functions, since I’ll only have a single row for any product_id.
As I mentioned before, the two downsides are:
1. The DBA drops the job, the job breaks (it could but I’ve never seen it happen in my schema) or the job never runs or runs too infrequently (job_queue_interval and job_queue_processes).
2. With the job running every 60 seconds the worst case scenario is that others won’t see the true “in stock” values for up to a minute.
One of my thoughts was to run the job more frequently (every 15 seconds). I know that in Oracle8i you have access to job_queue_interval but that isn’t available any more in 9i. Does anyone know how often the job queue is checked in 9i and above?
I’ve read just a little about advanced queues, I’m not sure if that is appropriate or not. It seems to me that if the “job” I’m currently running could be run immediately if there are rows committed to t_inventory_queue that would be ideal. The key is that only a single process ever processes rows from t_inventory_queue (something Oracle’s job functionality does for me right now).
-Tim
January 18th, 2006 at 6:35 am
Eddie,
If it was up to me I’d delete the question and redirect the OP to a forum — you might like to see if you can put a warning on the “Ask a new question” page to warn against specific question (or is there already one?), similar in purpose to the “Fair Warning” on AskTom.
January 18th, 2006 at 8:49 am
timboc, I agree with David.
But I answered your question. Just use a plain view with the group by. You can then use it in all of your other SQL. It’s not an inline or materialized.
Do you know what you’re doing? I think you are over complicating a simple task.
The table is there, just make a view using a group by. Simple enough. No Meterialized needed unless you end up with a huge table. To keep the table small I would do this perhaps.
DROP TABLE t_inventory_temp;
CREATE TABLE t_inventory_temp
AS SELECT * from t_inventory;
DELETE FROM t_inventory;
INSERT INTO t_inventory
SELECT product_id, sum(in_stock)
FROM t_inventory_temp;
(I would not drop t_inventory_temp as it could also be a temporary backup location. Nice to have for troubleshooting purposes etc.)
You’re done. Maybe reindex here?
That’s pretty simple and keeps the table small.
January 18th, 2006 at 9:35 am
One other thing you could do is when you begin your transaction, insert a dummy record into the t_inventoy table. Just issue some update statement against it. The table will be locked from updates but can still be read from. After the operation is commited all the changes that were waiting during the lock will then happen upon the table. This way you should not lose any records.
January 18th, 2006 at 10:35 am
If it was up to me I’d delete the question and redirect the OP to a forum
I won’t delete it, but this will be the first and last time I approve such questions (as I mentioned why in comment number 2 above).
you might like to see if you can put a warning on the “Ask a new question†page to warn against specific question
Great suggestion. The About page answers exactly what type of questions and answers should be submitted, however, I will look into adding such warning on the “new post” page as well.
January 18th, 2006 at 10:40 am
I appreciate everyones input into my question, it’s certainly given me some “food for thought”.
I also have a better understanding as to what types of questions are appropriate for this site as well.
-Tim
January 18th, 2006 at 11:25 am
In that case …
Tim,
The underlying problem here probably is that the performance in scanning a table of inventory transactions to calculate the balance is poor, so here’s some thoughts on that.
Firstly, I like the MV idea. There’s a certain risk in adopting it because you are potentially using code paths in the RDBMS that you haven’t used before, so your exposure to bugs in the Oracle code is higher. You might have trouble if you need to archive old transactions.
Secondly, if you want to calculate balances for a particular item based on inventory transactions then clustering the transactions together would be one option. Putting the table into a hash cluster based on the item identifier would do this nicely — again, new code paths being used.
Thirdly, you could adopt a hybrid strategy of calculating balances based on an intermittently-maintained balance table and a subset of your transactions … you might define the balance table as …
.. and populate it as …
You’d maybe want to update the balance table at 3am using a predicate such as …
… and then calculate the current balance based on a query like …
SELECT ITEM#, BALANCE_QTY+ (SELECT SUM(BALANCE_CHANGE_QTY) FROM INVENTORY_TRANSACTIONS IT WHERE IT.ITEM# = IB.ITEM# AND TRANSACTION_DATE > IB.AS_OF_DATE) FROM INVENTORY_BALANCE IB WHERE ITEM# = 12345This would allow you to archive inventory transactions, or reduce the amount of work required to calculate the balance dynamically if you had a very high number of transactions and you could efficiently query a subset of them by ITEM# and semi-bounded range of TRANSACTION_DATE.
This third strategy also extends to allowing manual adjustments to the inventory balance in another table — the world being what it is one often finds that balances are not what they should be.
January 18th, 2006 at 11:25 am
OOp — forgot to close the pre tag
January 18th, 2006 at 11:35 am
David, I did close the pre tag for you.
I will have to add some kind of “comment preview” functionality to the site.
Thanks.
January 22nd, 2006 at 1:39 am
WTF? One other thing you could do is when you begin your transaction, insert a dummy record into the t_inventoy table. Just issue some update statement against it. The table will be locked from updates but can still be read from. After the operation is commited all the changes that were waiting during the lock will then happen upon the table. This way you should not lose any records.
Not if we are still talking about Oracle.