Re: FIFO in Oracle

Hi Micheal,
I suggest u 2 different ways -

1. The FIFO table should have a column which will be populated by the sequence. And there will be an index on this column. Let's say we call this column as "request_number". There will be another table where the server process will store/update the last request_number processed. Everytime the server process is free to process the request, it will query this table and get the new request to be processed by adding 1 to the last request number. Then lock this table. And do the processing. At the end of the processing, add 1 to the last_request_number and commit.
2. Another way is to use the package DBMS_PIPE. The request process will put the request in the named pipe and the server process will read it. It will be in a FIFO fashion.

Hope this helps,

Ketan Shah.
Sr. Consultant,
Mellon Bond Associates, Pittsburgh, PA.
In article <6blrh2$8m6$1_at_tst.hk.super.net>,   myung_at_news.hk.super.net (Mr Michael Wai Kee Yung) wrote:
>
> Dear all,
>
> Our company is going to develop an Oracle application that requires to
> store requests in a database table (let's name in DB Queue) and later on
> another server program will pick up the requests and process in a FIFO
> fashion.
>
> The question is how can I do it in Oracle efficiently. It seems to me the
> server need to read all the requests and sort by time stamp when every
> time the server program is free to do something.
>
> Of course a sort for each fetch is costly. We could create a share memory
> table and store, say 100 requests to the memory and the server process
> from there. Still, you know, it require a sort for every 100 requests.
>
> Any idea how our company can improve this mechanism ?
>
> Many thanks in advance.
>
> Cheers,
> Michael \\8-)
>
> --
> +----------------------------------------------------+
> | Born To Be Wired : http://www.hk.super.net/~myung/ |
> +----------------------------------------------------+

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet