EXPLAIN EXTENDED

From Stack Overflow:

There is a table that stores signal statuses that come from different devices.

• SS1 and SS2 signals are inserted to table in random times
• If either of SS1 and SS2 signal statuses is up, then the resulting signal should be up
• If both SS1 and SS2 signal statuses are down, then resulting signal should be down

I want to prepare a query that shows the result signal status changes according to SS1 and SS2 signals

Each record deals with only one signal type here: either SS1 or SS2. To obtain the signal statuses we should query the cumulative values of previous records.

If a record describes a change in SS2, we should query for the most recent change to SS1 that had been recorded so far to obtain the SS1‘s current status.

In systems other than Oracle, the previous value of a signal could be easily queried using subselects with TOP / LIMIT clauses. But Oracle does not support correlated queries nested more than one level deep, and limiting a subquery result to a single record (which is required by a subquery) requires it (ORDER BY should be nested). This makes constructing such a subquery in Oracle quite a pain.

However, in Oracle, these things can be queries using analytics functions much more efficiently.

Let’s create a sample table:

Table creation details

BEGIN
        DBMS_RANDOM.seed(20100120);
END;
/
CREATE TABLE t_signal
        (
        id NOT NULL,
        signal NOT NULL,
        status NOT NULL,
        ts NOT NULL
        )
AS
SELECT  level,
        CASE WHEN DBMS_RANDOM.value < 0.5 THEN 'SS1' ELSE 'SS2' END,
        ROUND(DBMS_RANDOM.value),
        TO_DATE('20.01.2010', 'dd.mm.yyyy') - level / 86400
FROM    dual
CONNECT BY
        level <= 1000000
/
ALTER TABLE t_signal ADD CONSTRAINT pk_signal_id PRIMARY KEY (id)
/
CREATE INDEX ix_signal_ts_id ON t_signal (ts, id)
/

This table contains 1,000,000 records filled with random states of random signals once per second.

Each record describes a state of a certain signal at a given moment of time. To find out the state of another signal at that moment of time we need to know the state held by the latest record for that signal.

To find out that state we can employ analytical function, LAST_VALUE.

With a default RANGE (that is BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), this function just gives the value of the expression held by the current row, and, therefore, is quite useless: it gives the same result as a plain expression would give, without any functions.

If col is unique and defines the order of the rows, then LAST_VALUE(expression) OVER (ORDER BY col) is just a quite expensive synonym for expression.

However, this function’s behavior can be changed by adding IGNORE NULLS clause. This clause makes the function return the last value so far which is not a NULL.

Now, returning the cumulative value of any signal becomes quite simple. We should just just make two expressions which substitute NULL instead of the signal status for the wrong signals. The LAST_VALUE (IGNORE NULLS) over these expressions will persist until rewritten by the new states of their corresponding signals.

Let’s check it:

SELECT  *
FROM    (
        SELECT  /*+ FIRST_ROWS */
                s.*,
                DECODE(signal, 'SS1', status, NULL) AS exp1,
                DECODE(signal, 'SS2', status, NULL) AS exp2,
                LAST_VALUE(DECODE(signal, 'SS1', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss1,
                LAST_VALUE(DECODE(signal, 'SS2', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss2
        FROM    t_signal s
        ORDER BY
                ts, id
        ) s2
WHERE   rownum <= 15

SELECT STATEMENT
 COUNT STOPKEY
  VIEW
   WINDOW NOSORT
    TABLE ACCESS BY INDEX ROWID, 20100120_signal.T_SIGNAL
     INDEX FULL SCAN, 20100120_signal.IX_SIGNAL_TS_ID

In the resultset above, EXP1 and EXP2 show the changes in the signal states of SS1 and SS2, with NULL‘s if the current record does not describe a change in the appropriate signals.

LAST_VALUE (IGNORE NULLS) over these expressions show their cumulative values. These values can be used to calculate the resulting signal state.

Note that using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW made Oracle to use WINDOW NOSORT and this query is instant, since the LAST_VALUE can be buffered.

To calculate the resulting signal, we should just return the GREATEST of ss1 and ss2 (returned by LAST_VALUE):

SELECT  SUM(GREATEST(ss1, ss2))
FROM    (
        SELECT  s.*,
                DECODE(signal, 'SS1', status, NULL) AS exp1,
                DECODE(signal, 'SS2', status, NULL) AS exp2,
                LAST_VALUE(DECODE(signal, 'SS1', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss1,
                LAST_VALUE(DECODE(signal, 'SS2', status, NULL) IGNORE NULLS) OVER (ORDER BY ts, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss2
        FROM    t_signal s
        ORDER BY
                ts, id
        ) s2

SELECT STATEMENT
 SORT AGGREGATE
  VIEW
   WINDOW SORT
    TABLE ACCESS FULL, 20100120_signal.T_SIGNAL

For the sake of brevity, we select the SUM of the resulting signal states which gives us the number of records with resulting signal up. As it should be, this number roughly amounts to 75% of the total number of records.

The query uses a single sort (which in case of the whole table is faster than traversing the index), buffers the results and completes in 3 seconds which is almost as fast as a plain query with ORDER BY over the same dataset would complete.

If this article was useful to you, please put some effort into sharing it with the others.
I would really appreciate it.