{"id":347,"date":"2020-06-11T18:13:02","date_gmt":"2020-06-11T16:13:02","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=347"},"modified":"2020-06-17T10:06:07","modified_gmt":"2020-06-17T08:06:07","slug":"sql-query-for-queue-table-with-priorities","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2020\/06\/11\/sql-query-for-queue-table-with-priorities\/","title":{"rendered":"SQL query for queue table with priorities"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"534\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/800px-Weird_people_waiting_in_a_queue.jpg\" alt=\"\" class=\"wp-image-348\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/800px-Weird_people_waiting_in_a_queue.jpg 800w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/800px-Weird_people_waiting_in_a_queue-300x200.jpg 300w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/800px-Weird_people_waiting_in_a_queue-768x513.jpg 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/figure>\n\n\n\n<p>Queue tables are used to take care of events. There is a mechanism that insert rows and another that takes care of the existing rows, usually deleting them at the end.<\/p>\n\n\n\n<p>When possible one should use Oracle Advanced Queuing mechanism which takes care of managing the queue and a simple request will give you the next in the line.<\/p>\n\n\n\n<p>Some applications develop their own queuing systems and it is a good way to learn how queue works.<\/p>\n\n\n\n<p>Imagine a table with a list of elements and two specific columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>order of arrival<\/li><li>priority<\/li><\/ul>\n\n\n\n<p>The first to be served is the one that, having the highest priority, was the first to arrive. <\/p>\n\n\n\n<!--more-->\n\n\n\n<p>(It is not the fairest way to do it, as the ones with low priority might never be served. Ideally the priority should increase also with the wait time)<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Problem<\/h2>\n\n\n\n<p>At my customer we have one of these selft designed queues. And it is responsible for the most consuming query:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nUPDATE t_queue t1 \n   SET begtime = SYSDATE, \n       id = &#039;xxxx&#039;, \n       clerk = &#039;yyy&#039;\n WHERE queue_tag = &#039;*&#039; \n   AND id IS NULL \n   AND order_of_arrival = (SELECT MIN(order_of_arrival) \n                      FROM t_queue t2\n                     WHERE t1.queue_tag = queue_tag \n                       AND id IS NULL \n                       AND retries &lt; 10\n                       AND priority = (SELECT MAX(priority) \n                                         FROM t_queue t3\n                                        WHERE queue_tag = &#039;*&#039;\n                                          AND id IS NULL \n                                          AND retries &lt; 10));\n<\/pre><\/div>\n\n\n<p>So, the application tries to get, for any service (queue_tag), who was the first to arrive that has the highest priority.<\/p>\n\n\n\n<p>The table has many columns, some of them quite large. The primary key is order_of_arrival.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">First solution<\/h2>\n\n\n\n<p>So, in order to make the query faster, first I&#8217;ve created an index, avoiding to to read the whole table each time.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nCREATE INDEX idx_t_queue_1 ON t_queue (queue_tag, id, retries, priority, order_of_arrival)\n<\/pre><\/div>\n\n\n<p>The execution plan, when the table has about 20% of rows matching the conditiongs (queue_tag and id) is:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n----------------------------------------------------------------------------------------------\n| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------------------\n|   0 | UPDATE STATEMENT             |               |     1 |    14 |   235   (1)| 00:00:01 |\n|   1 |  UPDATE                      | T_QUEUE       |       |       |            |          |\n|*  2 |   TABLE ACCESS BY INDEX ROWID| T_QUEUE       |     1 |    14 |     2   (0)| 00:00:01 |\n|*  3 |    INDEX UNIQUE SCAN         | PK_T_QUEUE    |     1 |       |     1   (0)| 00:00:01 |\n|   4 |     SORT AGGREGATE           |               |     1 |    14 |            |          |\n|*  5 |      INDEX RANGE SCAN        | IDX_T_QUEUE_1 |  4000 | 56000 |   116   (0)| 00:00:01 |\n|   6 |       SORT AGGREGATE         |               |     1 |     9 |            |          |\n|*  7 |        INDEX RANGE SCAN      | IDX_T_QUEUE_1 | 19998 |   175K|   116   (0)| 00:00:01 |\n----------------------------------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p>I wanted to make this query also simpler and beautiful. For this I turned to my Trivadis colleagues and specially to <a rel=\"noreferrer noopener\" href=\"https:\/\/www.kibeha.dk\/\" target=\"_blank\">Kim Berg Hansen<\/a>, writer of <a rel=\"noreferrer noopener\" href=\"https:\/\/www.apress.com\/gp\/book\/9781484256169\" target=\"_blank\">Pratical Oracle SQL book<\/a>, who can make you to want to frame a SQL query on the wall. Kim suggested me this beautiful simple query:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nUPDATE t_queue \n   SET begtime = SYSDATE, \n       id = &#039;xxx&#039;, \n       clerk = &#039;yyyy&#039; \n       WHERE rowid = (\n           SELECT rowid FROM t_queue \n           WHERE queue_tag = &#039;*&#039; \n           AND id IS NULL \n           AND retries &lt; 10\n           ORDER BY priority DESC, order_of_arrival\n           FETCH FIRST 1 ROW ONLY\n           );\n<\/pre><\/div>\n\n\n<p>The resulting execution plan was now reading only once the index:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n---------------------------------------------------------------------------------------------\n| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------------------------\n|   0 | UPDATE STATEMENT            |               |     1 |    26 |   119   (2)| 00:00:01 |\n|   1 |  UPDATE                     | T_QUEUE       |       |       |            |          |\n|   2 |   TABLE ACCESS BY USER ROWID| T_QUEUE       |     1 |    26 |     1   (0)| 00:00:01 |\n|*  3 |    VIEW                     |               |     1 |    25 |   118   (2)| 00:00:01 |\n|*  4 |     WINDOW SORT PUSHED RANK |               | 19998 |   507K|   118   (2)| 00:00:01 |\n|*  5 |      INDEX RANGE SCAN       | IDX_T_QUEUE_1 | 19998 |   507K|   116   (0)| 00:00:01 |\n---------------------------------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p>Interesting enough, when testing 1000 consecutive runs, the original query was faster than the FETCH FIRST ROW version:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Time subqueries: 11,15 s<\/li><li>Time fetch first: 12,23 s<\/li><\/ul>\n\n\n\n<p>But this was only when there were &gt;10% of rows matching the conditions. If there are &lt;10% of rows where it needs to find the first one, then the FETCH FIRST ROW is faster.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Kim Berg Hansen&#8217; solution<\/h2>\n\n\n\n<p>Kim did some more investigation and asked me to test with two other indexes, which would force a better execution plan. First:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE INDEX t_myqueue_idx_fnc_asc ON t_queue (\n   queue_tag, CASE WHEN id IS NULL THEN 1 END, CASE WHEN retries &lt; 10 THEN 1 END, priority, order_of_arrival\n);\n<\/pre><\/div>\n\n\n<p>As this is a function based index, the queries needed to be changed to make use of it. The new queries would be:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nUPDATE t_queue q1\n   SET begtime = SYSDATE, \n       id = &#039;xxx&#039;, \n       clerk = &#039;yyyy&#039;\n   WHERE q1.order_of_arrival = (\n      SELECT MIN(q2.order_of_arrival)\n      FROM t_queue q2\n      WHERE q2.queue_tag = &#039;*&#039; \n        AND CASE WHEN q2.id IS NULL THEN 1 END = 1 \n        AND CASE WHEN q2.retries &lt; 10 THEN 1 END = 1\n        AND q2.priority = (\n         SELECT MAX(q3.priority) \n         FROM t_queue q3 \n         WHERE q3.queue_tag = &#039;*&#039;\n           AND CASE WHEN q3.id IS NULL THEN 1 END = 1 \n           AND CASE WHEN q3.retries &lt; 10 THEN 1 END = 1\n         )\n     );\n<\/pre><\/div>\n\n\n<p>resulting on execution plan:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n--------------------------------------------------------------------------------------------------------\n| Id | Operation                          | Name                  | Rows |Bytes |Cost (%CPU)| Time     |\n--------------------------------------------------------------------------------------------------------\n|  0 | UPDATE STATEMENT                   |                       |    1 |   14 |    8   (0)| 00:00:01 |\n|  1 |  UPDATE                            | T_QUEUE               |      |      |           |          |\n|  2 |   TABLE ACCESS BY INDEX ROWID      | T_QUEUE               |    1 |   14 |    2   (0)| 00:00:01 |\n|* 3 |    INDEX UNIQUE SCAN               | PK_T_QUEUE            |    1 |      |    1   (0)| 00:00:01 |\n|  4 |     SORT AGGREGATE                 |                       |    1 |   15 |           |          |\n|  5 |      FIRST ROW                     |                       |    1 |   15 |    3   (0)| 00:00:01 |\n|* 6 |       INDEX RANGE SCAN (MIN\/MAX)   | T_MYQUEUE_IDX_FNC_ASC |    1 |   15 |    3   (0)| 00:00:01 |\n|  7 |        SORT AGGREGATE              |                       |    1 |   10 |           |          |\n|  8 |         FIRST ROW                  |                       |    1 |   10 |    3   (0)| 00:00:01 |\n|* 9 |          INDEX RANGE SCAN (MIN\/MAX)| T_MYQUEUE_IDX_FNC_ASC |    1 |   10 |    3   (0)| 00:00:01 |\n--------------------------------------------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p>and<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nUPDATE t_queue q1\nSET begtime = SYSDATE, \n    id = &#039;xxx&#039;, \n    clerk = &#039;yyyy&#039;\nWHERE q1.rowid = (\n   SELECT rowid\n   FROM t_queue\n   WHERE queue_tag = &#039;*&#039;\n   AND CASE WHEN id IS NULL THEN 1 END = 1\n   AND CASE WHEN retries &lt; 10 THEN 1 END = 1\n   ORDER BY queue_tag, CASE WHEN id IS NULL THEN 1 END, CASE WHEN retries &lt; 10 THEN 1 END, priority DESC, order_of_arrival\n   FETCH FIRST 1 ROW ONLY\n);\t\t\t \n<\/pre><\/div>\n\n\n<p>With execution plan:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n-----------------------------------------------------------------------------------------------------\n| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------------------------\n|   0 | UPDATE STATEMENT            |                       |     1 |    26 |    55   (4)| 00:00:01 |\n|   1 |  UPDATE                     | T_QUEUE               |       |       |            |          |\n|   2 |   TABLE ACCESS BY USER ROWID| T_QUEUE               |     1 |    26 |     1   (0)| 00:00:01 |\n|*  3 |    VIEW                     |                       |     1 |    25 |    54   (4)| 00:00:01 |\n|*  4 |     WINDOW SORT PUSHED RANK |                       | 11991 |   316K|    54   (4)| 00:00:01 |\n|*  5 |      INDEX RANGE SCAN       | T_MYQUEUE_IDX_FNC_ASC | 11991 |   316K|    52   (0)| 00:00:01 |\n-----------------------------------------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p>The time with subqueries was now using INDEX RANGE SCAN(MIN\/MAX) and was incredibly faster, while FETCH FIRST was still using WINDOWS SORT PUSHED RANK and the timing did not change:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Time subqueries: 0,57 seconds (any % of matching rows)<\/li><li>Time fetch first: 12,88 seconds (20% matching rows)<\/li><li>Time fetch first: 7,54 (10% matching rows)<\/li><\/ul>\n\n\n\n<p>The other index, with priority column in different order, would be:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE INDEX t_myqueue_idx_fnc_dsc ON t_queue (\n   queue_tag, CASE WHEN id IS NULL THEN 1 END, CASE WHEN retries &lt; 10 THEN 1 END, priority DESC, order_of_arrival\n);\n<\/pre><\/div>\n\n\n<p>Which resulted in this execution plan back to INDEX RANGE SCAN for the subqueries version:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n------------------------------------------------------------------------------------------------------\n| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |\n------------------------------------------------------------------------------------------------------\n|   0 | UPDATE STATEMENT             |                       |     1 |    14 |   149   (0)| 00:00:01 |\n|   1 |  UPDATE                      | T_QUEUE               |       |       |            |          |\n|   2 |   TABLE ACCESS BY INDEX ROWID| T_QUEUE               |     1 |    14 |     2   (0)| 00:00:01 |\n|*  3 |    INDEX UNIQUE SCAN         | PK_T_QUEUE            |     1 |       |     1   (0)| 00:00:01 |\n|   4 |     SORT AGGREGATE           |                       |     1 |    15 |            |          |\n|*  5 |      INDEX RANGE SCAN        | T_MYQUEUE_IDX_FNC_DSC |  3999 | 59985 |    26   (0)| 00:00:01 |\n|   6 |       SORT AGGREGATE         |                       |     1 |    10 |            |          |\n|*  7 |        INDEX RANGE SCAN      | T_MYQUEUE_IDX_FNC_DSC | 19996 |   195K|   121   (0)| 00:00:01 |\n------------------------------------------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p>and with FETCH FIRST version, a new plan now using WINDOW BUFFER PUSHED RANK:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n------------------------------------------------------------------------------------------------------\n| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |\n------------------------------------------------------------------------------------------------------\n|   0 | UPDATE STATEMENT             |                       |     1 |    26 |   122   (0)| 00:00:01 |\n|   1 |  UPDATE                      | T_QUEUE               |       |       |            |          |\n|   2 |   TABLE ACCESS BY USER ROWID | T_QUEUE               |     1 |    26 |     1   (0)| 00:00:01 |\n|*  3 |    VIEW                      |                       |     1 |    25 |   121   (0)| 00:00:01 |\n|*  4 |     WINDOW BUFFER PUSHED RANK|                       | 19996 |   527K|   121   (0)| 00:00:01 |\n|*  5 |      INDEX RANGE SCAN        | T_MYQUEUE_IDX_FNC_DSC | 19996 |   527K|   121   (0)| 00:00:01 |\n------------------------------------------------------------------------------------------------------\n<\/pre><\/div>\n\n\n<p>In this case the results were:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Time subqueries: 8,01 (20% matching rows)<\/li><li>Time fetch first: 10,89 (20% matching rows)<\/li><li>Time subqueries: 3,84 (10% matching rows)<\/li><li>Time fetch first: 5,18 (10% matching rows)<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Addendum &#8211; Mikhail Velikikh suggestion<\/h2>\n\n\n\n<p>Mikhail Velikikh quickly commented to this post, warning me about an error on a query in the post (thanks!) and, using the descending function based index, suggested a more beautiful solution: <\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.rowid = (\n           select * from (\n               select rowid\n               from t_myqueue\n               where queue_tag = &#039;*&#039;\n               and case when id is null then 1 end = 1\n               and case when retrycount &amp;lt; 10 then 1 end = 1\n               order by queue_tag, case when id is null then 1 end, case when retrycount &amp;lt; 10 then 1 end, priority desc, order_of_arrival\n               )\n          where rownum=1 );\n<\/pre><\/div>\n\n\n<p>Indeed at the first tests, Mikhail&#8217; solution become the fastest when his query was run immediately after the index creation &#8211; doing a range scan and stop count, with 0.3 seconds. In most of the cases, as I did not run immediately this test case, then it becomes slow as it does a fast full scan.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Final results<\/h2>\n\n\n\n<p>We have now:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>One table with 100k rows<\/li><li>PK is a sequence related to order of arrival<\/li><li>There is a priority column<\/li><li>3 possible indexes &#8211; one simple composite, two function based: ascending and descending<\/li><li>3 amount of matching rows &#8211; 10%, 20% and 100%<\/li><li>6 different update queries &#8211; one set for the simple index: using subqueries, using fetch first, using stop count; another set for the function based indexes, also the three different forms: subqueries, fetch first and stop count.<\/li><\/ul>\n\n\n\n<p>I&#8217;ve run more than 5 times a procedure that executes 1000 times the same update query and check how long it took. In the table and graph below I&#8217;ve always used the best result of each of the runs. Note: the results are better than on the text of the blog above, as I&#8217;ve now done much more runs and assured each time there was only one index on the table, apart of the primary key.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"562\" height=\"428\" src=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/image.png\" alt=\"\" class=\"wp-image-362\" srcset=\"https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/image.png 562w, https:\/\/anjo.pt\/keyword-oracle\/wp-content\/uploads\/sites\/3\/2020\/06\/image-300x228.png 300w\" sizes=\"auto, (max-width: 562px) 100vw, 562px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>When possible, and if a query like this really needs to be improved, it is good idea to try using a function based index and changing the query code. It will not look so nicely, though. <\/p>\n\n\n\n<p>With an FBI ascending index, and subqueries we get INDEX RANGE SCAN (MIN\/MAX) access. The speed of the query was constantly impressively fast: ~0,5 seconds for 1000 executions, in any situation.<\/p>\n\n\n\n<p>With an FBI descending index, a bit nicer looking query with INDEX RANGE SCAN and STOP COUNT access. The speed was impressive, at ~0,3 or ~0.4 seconds. Unfortunately this great execution plan was not always chosen by the optimizer, mainly when the query was not immediately run after the index creation (did have then some fragmentation?). In that case was doing INDEX FAST FULL SCAN and performance was on the dozens of seconds.<\/p>\n\n\n\n<p>When you don&#8217;t want to use function based index because it will likely not be used by any other query, then the best solution was the subqueries version of the SQL, at least 40% faster than the other SQL versions.<\/p>\n\n\n\n<p>A big thanks to Kim Berg Hansen for all his help and insights and to Mikhail Velikikh for the new suggestion. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Test case<\/h2>\n\n\n\n<p>In case you want to test at your own lab, below you can find the complete test case.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n\/* Create Queue table *\/\ndrop table t_myqueue purge;\ncreate table t_myqueue (\n   order_of_arrival   number\n , priority           number not null\n , retrycount         number default 0 not null\n , queue_tag          varchar2(1000) default &#039;*&#039; not null\n , id                 varchar2(64)\n , filler1            varchar2(1000)\n , filler2            varchar2(1000)\n , CONSTRAINT pk_t_myqueue PRIMARY KEY (order_of_arrival)\n);\n\n\n\/* Create indexes (choose one) *\/\nDROP INDEX idx_t_myqueue_1;\nDROP INDEX idx_t_myqueue_fbi_asc;\nDROP INDEX idx_t_myqueue_fbi_dsc;\nCREATE INDEX idx_t_myqueue_1 ON t_myqueue (queue_tag, id, priority, retrycount, order_of_arrival);\nCREATE INDEX idx_t_myqueue_fbi_asc ON t_myqueue (\n   queue_tag, CASE WHEN id IS NULL THEN 1 END, CASE WHEN retrycount &lt; 10 THEN 1 END, priority, order_of_arrival\n);\nCREATE INDEX idx_t_myqueue_fbi_dsc ON t_myqueue (\n   queue_tag, CASE WHEN id IS NULL THEN 1 END, CASE WHEN retrycount &lt; 10 THEN 1 END, priority DESC, order_of_arrival\n);\n\n\n\/* Clear, fill table, gather stats - uncomment one line *\/\ntruncate table t_myqueue;\ninsert into t_myqueue\n  select level as order_of_arrival, \n    floor(dbms_random.value(1, 6)) as priority, \n    0 as retrycount,\n    &#039;*&#039; queue_tag,\n--  \/* 100% matches *\/ null as id, \n--  \/*  20% matches *\/ case when mod(rownum, 5)=0 then null else sys_guid() end as id,\n    \/*  10% matches *\/ case when mod(rownum, 10)=0 then null else sys_guid() end as id,\n    rpad(&#039;xxhx&#039;,1000,&#039;xder&#039;) as filler1,\n    rpad(&#039;xdsxx&#039;,1000,&#039;xgdder&#039;) as filler2\n  from dual\n  connect by level &lt;=100000;\ncommit;\nexec dbms_stats.gather_table_stats(user,&#039;t_myqueue&#039;);\n\n\/* Create testing procedure *\/\nCREATE OR REPLACE PROCEDURE time_sql_x (title varchar2, sqltxt varchar2) AS\n   timestart INTEGER;\n   counter INTEGER; \nBEGIN\n   dbms_output.enable;\n   timestart := dbms_utility.get_time();\n   FOR counter IN 1..1000\n   LOOP\n     execute immediate(sqltxt);\n   END LOOP;   \n   dbms_output.put_line(title||&#039;: &#039; || to_char((dbms_utility.get_time() - timestart)\/100,&#039;0.99&#039;) || &#039; seconds&#039;);\n   rollback;\nEND;\n\/ \n\n\/* Enable output *\/\nset server output on;\n\n\/* Check table matches *\/\nselect total, matches, to_char(matches\/total*100 ||&#039;%&#039;) PCT from (\nselect count(*) total, count(case when id is null and queue_tag=&#039;*&#039; and retrycount&lt;10 then 1 end) matches from t_myqueue);\n\n\/* Run tests *\/\nbegin time_sql_x(&#039;Simple subqueries&#039;,q&#039;&#x5B;\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.order_of_arrival = (\n           select min(q2.order_of_arrival)\n           from t_myqueue q2\n           where q2.queue_tag = &#039;*&#039;\n           and q2.id is null\n           and q2.retrycount &lt; 10\n           and q2.priority = (\n              select max(q3.priority)\n              from t_myqueue q3\n              where q3.queue_tag = &#039;*&#039;\n              and q3.id is null\n              and q3.retrycount &lt; 10\n           )\n        )\n]&#039;); end;\n\/\n\nbegin time_sql_x(&#039;Simple fetch first&#039;,q&#039;&#x5B;\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.rowid = (\n           select rowid\n           from t_myqueue\n           where queue_tag = &#039;*&#039;\n           and id is null\n           and retrycount &lt; 10\n           order by priority desc, order_of_arrival\n           fetch first 1 row only\n        )\n]&#039;); end;\n\/\n\nbegin time_sql_x(&#039;Simple Stop count&#039;,q&#039;&#x5B;\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.rowid = (\n           select * from (\n               select rowid\n               from t_myqueue\n               where queue_tag = &#039;*&#039;\n               and id is null\n               and retrycount &lt; 10\n               order by priority desc, order_of_arrival\n               )\n          where rownum=1\n           )\n]&#039;); end;\n\/\n\nbegin time_sql_x(&#039;FBI - Subqueries&#039;,q&#039;&#x5B;\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.order_of_arrival = (\n           select min(q2.order_of_arrival)\n           from t_myqueue q2\n           where q2.queue_tag = &#039;*&#039;\n           and case when q2.id is null then 1 end = 1\n           and case when q2.retrycount &lt; 10 then 1 end = 1\n           and q2.priority = (\n              select max(q3.priority)\n              from t_myqueue q3\n              where q3.queue_tag = &#039;*&#039;\n              and case when q3.id is null then 1 end = 1\n              and case when q3.retrycount &lt; 10 then 1 end = 1\n           )\n        )\n]&#039;); end;\n\/\n\nbegin time_sql_x(&#039;FBI - Fetch First&#039;,q&#039;&#x5B;\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.rowid = (\n           select rowid\n           from t_myqueue\n           where queue_tag = &#039;*&#039;\n           and case when id is null then 1 end = 1\n           and case when retrycount &lt; 10 then 1 end = 1\n           order by queue_tag, case when id is null then 1 end, case when retrycount &lt; 10 then 1 end, priority desc, order_of_arrival\n           fetch first 1 row only\n        )\n]&#039;); end;\n\/\n\nbegin time_sql_x(&#039;FBI - Stop count&#039;,q&#039;&#x5B;\n        update t_myqueue q1\n        set id = &#039;x&#039;\n        where q1.rowid = (\n           select * from (\n               select rowid\n               from t_myqueue\n               where queue_tag = &#039;*&#039;\n               and case when id is null then 1 end = 1\n               and case when retrycount &lt; 10 then 1 end = 1\n               order by queue_tag, case when id is null then 1 end, case when retrycount &lt; 10 then 1 end, priority desc, order_of_arrival\n               )\n          where rownum=1\n           )\n]&#039;); end;\n\/\n\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Queue tables are used to take care of events. There is a mechanism that insert rows and another that takes care of the existing rows, usually deleting them at the end. When possible one should use Oracle Advanced Queuing mechanism which takes care of managing the queue and a simple request will give you the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,44],"tags":[],"class_list":{"0":"post-347","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-sql","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=347"}],"version-history":[{"count":15,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/347\/revisions"}],"predecessor-version":[{"id":371,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/347\/revisions\/371"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}