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)
/
[/sourcecode]
</div>
This table contains <strong>1,000,000</strong> 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, <code>LAST_VALUE</code>.
With a default <code>RANGE</code> (that is <code>BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code>), 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 <code>col</code> is unique and defines the order of the rows, then <code>LAST_VALUE(expression) OVER (ORDER BY col)</code> is just a quite expensive synonym for <code>expression</code>.
However, this function's behavior can be changed by adding <code>IGNORE NULLS</code> clause. This clause makes the function return the last value so far which is not a <code>NULL</code>.
Now, returning the cumulative value of any signal becomes quite simple. We should just just make two expressions which substitute <code>NULL</code> instead of the signal status for the <q>wrong</q> signals. The <code>LAST_VALUE (IGNORE NULLS)</code> 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
[/sourcecode]
<div class="terminal">
<table class="terminal">
<tr>
<th>ID</th><th>SIGNAL</th><th>STATUS</th><th>TS</th><th>EXP1</th><th>EXP2</th><th>SS1</th><th>SS2</th></tr>
<tr><td class="double_precision">1000000</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:20</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision"></td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999999</td><td class="char">SS2</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:21</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td></tr>
<tr><td class="double_precision">999998</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:22</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td></tr>
<tr><td class="double_precision">999997</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:23</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">0</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999996</td><td class="char">SS1</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:24</td><td class="double_precision">1</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999995</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:25</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">1</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999994</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:26</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999993</td><td class="char">SS1</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:27</td><td class="double_precision">1</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999992</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:28</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999991</td><td class="char">SS2</td><td class="double_precision">1</td><td class="date">08.01.2010 10:13:29</td><td class="double_precision"></td><td class="double_precision">1</td><td class="double_precision">0</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999990</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:30</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">1</td></tr>
<tr><td class="double_precision">999989</td><td class="char">SS2</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:31</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td><td class="double_precision">0</td></tr>
<tr><td class="double_precision">999988</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:32</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td></tr>
<tr><td class="double_precision">999987</td><td class="char">SS2</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:33</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td><td class="double_precision">0</td></tr>
<tr><td class="double_precision">999986</td><td class="char">SS1</td><td class="double_precision">0</td><td class="date">08.01.2010 10:13:34</td><td class="double_precision">0</td><td class="double_precision"></td><td class="double_precision">0</td><td class="double_precision">0</td></tr>
<tr class="statusbar"><td colspan="100">15 rows fetched in 0.0012s (0.0007s)</td></tr>
</table>
</div>
<pre>
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
</pre>
In the resultset above, <code>EXP1</code> and <code>EXP2</code> show the changes in the signal states of <code>SS1</code> and <code>SS2</code>, with <code>NULL</code>'s if the current record does not describe a change in the appropriate signals.
<code>LAST_VALUE (IGNORE NULLS)</code> over these expressions show their cumulative values. These values can be used to calculate the resulting signal state.
Note that using <code>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code> made <strong>Oracle</strong> to use <code>WINDOW NOSORT</code> and this query is instant, since the <code>LAST_VALUE</code> can be buffered.
To calculate the resulting signal, we should just return the <code>GREATEST</code> of <code>ss1</code> and <code>ss2</code> (returned by <code>LAST_VALUE</code>):
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
| SUM(GREATEST(SS1,SS2)) |
| 750450 |
| 1 row fetched in 0.0003s (3.0781s) |
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.
Leave a Reply
Subscribe in a reader