Oracle SELECT with multiple AND conditions for multiple columns (INTERSECT alternative)

Question

How do I solve the following problem:

Imagine we have a large building with about 100 temperature readers and each one collects the temperature every minute.

I have a rather large table (~100m) rows with the following columns:

Table TempEvents:

 Timestamp   - one entry per minute
 Reader ID   - about 100 separate readers
 Temperature - Integer (-40 -> +40)

Timestamp and Reader ID are primary+secondary keys to the table. I want to perform a query which finds all the timestamps where
reader_01 = 10 degrees,
reader_02 = 15 degrees and
reader_03 = 20 degrees.

In other words something like this:

SELECT Timestamp FROM TempEvents
 WHERE (readerID=01 AND temperature=10)
 AND   (readerID=02 AND temperature=15)
 AND   (readerID=03 AND temperature=20)

==> Resulting in a list of timestamps:

Timestamp::
2016-01-01 05:45:00
2016-02-01 07:23:00
2016-03-01 11:56:00
2016-04-01 23:21:00

The above query returns nothing since a single row does not include all conditions at once. Using OR in between the conditions is also not producing the desired result since all readers should match the condition.

Using INTERSECT, I can get the result by:

SELECT * FROM
(SELECT Timestamp FROM TempEvents WHERE readerID=01 AND temperature=10
 INTERSECT SELECT Timestamp FROM TempEvents WHERE readerID=02 AND temperature=15
 INTERSECT SELECT Timestamp FROM TempEvents WHERE readerID=03 AND temperature=20
 )
GROUP BY Timestamp ORDER BY Timestamp ASC;

The above query is extremely costly and takes about 5 minutes to execute.

Is there a better (quicker) way to get the result?

SELECT Timestamp FROM TempEvents WHERE (readerID=01 AND temperature=10) OR (readerID=02 AND temperature=15) OR (readerID=03 AND temperature=20)? — neutrino, Sep 01 '16 at 09:17
Not sure, but what about: `SELECT Timestamp FROM TempEvents WHERE (readerid, temperature) IN ((1,10), (2,15), (3,20)) group by readerid, temperature having count(distinct readerid) = 3;` — , Sep 01 '16 at 09:20
To be honest for the above problem the simplest is what @neutrino suggested. What's the issue you getting while using his solution — XING, Sep 01 '16 at 09:24
@Raj_Te: the solution using `OR` will also return timestamps where only **one** of those conditions is true. HHAnsson wants those where **all** conditions are true. — , Sep 01 '16 at 09:27
@Horse..In that case yours is the most elegant way to solve this. Joining tables or doing intersects will increase the overheads. — XING, Sep 01 '16 at 09:31
Thanks a lot guys for commenting on this! I tried the horse without a name suggestion and compared to my own testing here: using 10 distinct readers (I can have up to 50..) with intersect gave me 592 rows and it took 43 seconds to run. Using the suggestion, I got the same result set (592 rows) and it took nearly half (24 seconds), group by the timestamp value though. So a huge improvement. Thanks again. — HHansson, Sep 01 '16 at 09:56
Just tried the Mike solution below and I got the same result set, and it only took 9 seconds to run.... Great improvement!!! — HHansson, Sep 01 '16 at 10:05

Iain · Answer 1 · 2016-09-01T09:26:49.437

2

I just tried this in Oracle DB and it seems to work:

SELECT Timestamp FROM TempEvents
 WHERE (readerID=01 AND temperature=10)
 OR   (readerID=02 AND temperature=15)
 OR   (readerID=03 AND temperature=20)

Make sure to only change the AND outside of parenthesis

edited Sep 01 '16 at 09:26

answered Sep 01 '16 at 09:25

Iain

387
2
12

you are missing a group by expression and a having clause to mimic the "AND" – Nemeros Sep 01 '16 at 09:26

score 0 · Answer 2 · answered Sep 01 '16 at 09:21

If the number of readers you have to query is not too large you might try using a join-query like

select distinct Timestamp
  from TempEvents t1
  join TempEvents t2 using(Timestamp)
  join TempEvents t3 using(Timestamp)
 where t1.readerID=01 and t1.temperature = 10
   and t2.readerID=02 and t2.temperature = 15
   and t3.readerID=03 and t3.temperature = 20

But to be honest I doubt it will perform better than your INTERSECT-query.

score 0 · Accepted Answer · answered Sep 01 '16 at 09:30

0

Try this:

with Q(readerID,temperature) as(
 select 01, 10 from dual
 union all
 select 02,15 from dual
 union all
 select 03,20 from dual
)
select Timestamp FROM TempEvents T, Q
 where T.readerID=Q.readerID and T.temperature=Q.temperature
 group by Timestamp
having count(1)=(select count(1) from Q)

Perhaps this will give a better plan than using OR or IN clause.

answered Sep 01 '16 at 09:30

Mike

1,985
1
8
14

Thanks a lot Mike, I tried this and it only took 9 seconds compared to 43 seconds using my intersect alternative. Great improvement!!!! – HHansson Sep 01 '16 at 10:08

Oracle SELECT with multiple AND conditions for multiple columns (INTERSECT alternative)

3 Answers3