Oracle Select 查询以供应用程序同时访问
目前正在开发一个应用程序，其中将跨多个应用程序访问同一个表。进程 Message_Processor 将访问名为 Message_Details 的表。 消息详情的结构如下
| Order Id | Processed_By | Processed_Flag |
|---|---|---|
| O1 | MP1 | Y |
| O2 | MP3 | Y |
| O3 | MP2 | Y |
| O1 | N | |
| O2 | N | |
| O1 | N | |
| O4 | N |
Order Id ->代表订单的标识符。相同的订单可以重复，因此您会看到类似的多个 id。
处理者->表示已执行特定 Order 的进程。仅当同一进程执行了较早的类似订单或该订单根本没有被任何消息处理器处理过时，该订单才能由该进程处理。在上面的示例中， O1 已由 MP1 处理，因此 O1 的任何其他实例将仅由 MP1 处理。类似地，O4还没有被任何处理器处理过，因此它可以被任何消息处理器处理。
已处理标志 ->如果已处理则设置为 Y，如果未处理则设置为 N。
要求是编写一个查询，其中处理器说 MP1 能够识别上面未处理消息中的哪个订单可以由它处理。从上表可知，MP1 只能处理 O1 或 O4（不能处理 O2/O3，因为它们已被 MP3/MP2 处理）。
一种方法是触发单个查询来获取尚未处理的订单 ID，然后检查它们是否已被 MP1 之前处理过，或者检查它们是否尚未被任何处理器处理过。然而，这并不是一种非常有效的方法。
有人可以帮助如何以非常有效的方式实现这一目标。
Currently working on an application where a same table will be accessed across multiple applications. Process Message_Processor will be accessing a table called Message_Details.
Structure of Message Details is as follows
| Order Id | Processed_By | Processed_Flag |
|---|---|---|
| O1 | MP1 | Y |
| O2 | MP3 | Y |
| O3 | MP2 | Y |
| O1 | N | |
| O2 | N | |
| O1 | N | |
| O4 | N |
Order Id -> Represents the identifier of an order . A same order can be repeated and hence you see similar multiple id's.
Processed By -> Represents the process which has executed a particular Order . An order can only be processed by a process if either an earlier similar order was executed by the same process or the order has not been processed at all by any message processor. In above example O1 has been processed by MP1 , so any further instance of O1 will only be processed by MP1. Similarly O4 has not been processed by any processor, so it can be processed by any Message processor.
Processed Flag -> Set to Y if processed and N if not processed.
Requirement is to write a query where a processor say MP1 is able to identify which order from the above unprocessed messages can be processed by it. From above table MP1 can only process either O1 or O4 ( Not O2/O3 as they have ben processed by MP3/MP2).
One way is to fire individual query to get the order id that have not been processed and then check whether they have been processed by MP1 earlier or to check whether they have not been processed by any processor till now. However its not a very efficient way to do it.
Can someone help in how can this be achieved in a very efficient manner.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您在 (PROCESSED, PROCESSED_BY) 上有索引，那么查询 which is:
将非常高效。
更大的挑战是多个处理器将全部看到那些进程为“N”的行。查看 SKIP LOCKED 语法来构建一些管理此功能的工具。
If you have an index on (PROCESSED, PROCESSED_BY) then query which is:
will be very efficient.
A bigger challenge will be that multiple processors will all see those rows with proceesed being "N". Check out the SKIP LOCKED syntax to build some facilities managing this.