Assumptions: we have 100,000 Orders which hold the status = Locked | unlocked for update.

when the order is created it is unlocked. We can also create filter saying "when delivery time - current time < 10 hours, lock the order", lets say its called as Window time (10 hours)

To do this, there is a scheduler which will update the order if the condition to lock the order is true,

What is the efficient way to achieve this?

--- one solution, lets brain storm ---

1. select the order with status = unlocked (locked status need not be locked again).
2. See if the order is supposed to lock, lock if the condition of Window is true otherwise, leave it unlocked.

repeat this every 2 minutes, this seems like a weired idea. Db has to query all the orders in step 1 to get the list of orders which are unlocked. (performance will suck as orders will be increasing day by day, RIGHt).

--------- Please post your solutions to achieve this ---
* I was thinking of creating a view with orders having unlocked status ..
..
.. your suggestions ?

If you have an index on status and delivery_time then a straight update like this should be fine:

update mytable set status = 'locked' where status <> 'locked' and (delivery_time - 10 hours < current time

Andy

After 3 months there were, say 1000,000 order in the system, of which I assume 1000 will be in "unlocked status" others will either be locked or Completed.

No is it a good idea to query 1000,000 order to find those 1000 which we are concerned about ??

-- What if I create a view like

create view from orders table where status = "not locked".

The scheduler which update the order status will only look at the view, and will update it to "Locked" (WINDOW condition).

How about that?
- Questions: View is just a logical view, that mean when we query a view, we are actually querying the table.

eg. following the same examle:

Which will run faster:

update orders(table) set orderStatus = locked where order status = "unlocked" and (some condition)

update orderView (view) set orderStatus = locked where (some condition).

* orderView is view with orders having status = "unlocked".


Any suggestions.

A view is a LOGICAL object, so when you reference a view it is querying the physical table to get the data that is requested by the view. As Andy stated an index on the status should give you quite satisfactory performance. I have worked on apps where we had a few hundred million rows, but were looking for the few thousand that had a particular status and the performance was quite satisfactory for automated processes that ran all day long.

What about materialized views ?? according to our DBA we already have lot of indexes over the table, and we do want to avoid creating a new one.

any suggestions.
thanks,

Materialized views could probably help you as well, but those have to be maintained. I believe that maintaining MQTs (especially REFRESH IMMEDIATE ones) is much more heavy than maintaining an additional index.

Also, you could/should double check if you already have an index that includes the STATUS column. If so, you may also have the option to write your query in such a way that this index is used. Or you rearrange the columns in this index so that STATUS becomes the most significant column in the index, allowing you to do a partial index exploitation (with the number of matching columns being less than the number of columns in the index).

Furthermore, you could run your workload through the DB2 Design Advisor. Maybe you have excessive indexes that are not used, maybe different combinations of indexes are overall better.

p.s: A view is just what it's name implies - a (different) view on the actual data.