Oracle Database

ORACLE-WIKI.NET

Introduction

I recently came across an issue at a site, whereby a piece of code was running out of control, looping around very fast outputting messages to a table. For example

We can see in the table above that Customer ID 123456 has a number of Delivered messages interspersed with Not Delivered messages.

There was clearly a bug in the code, but I wanted to see if there was a pattern to the message inserts. For example, was there 10 Not Delivered messages in between each Delivered message or was it a random number of Not Delivered messages in between the Delivered messages.

Its important to note here we were not looking at a table with 10 rows. We were looking at a table with 10m rows and at least 50k messages for Customer ID 123456.

Below is the query I used to display the number of messages in between each Delivered message for the customer ID 123456. This query is useful for all kinds of message analysis or row analysis that match this kind of criteria. (Which is more common than you may think).

SQL Statement

SELECT message_desc, COUNT(*) AS message_count
FROM (
    SELECT customer_id, message_desc, pk_id
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY pk_id) - 
           ROW_NUMBER() OVER (PARTITION BY customer_id, message_desc ORDER BY pk_id) AS grp
    FROM incoming_messages
) t
WHERE message_desc IN ('NOT DELIVERED', 'DELIVERED') AND customer_id = 123456
GROUP BY customer_id, message_desc, grp
ORDER BY MIN(pk_id);

Example Output

We can see that we have 4 Not Delivered messages in between the first and second Delivered message and 3 Not Delivered messages between the second and third Delivered messages

Published 21st January 2024