CODEWITHSUNDEEP
sqloracle-databaseintersectrelational-division
HHansson
HHansson

Reputation: 3

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

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?

Upvotes: 0

Views: 23786

Answers (3)

Mike
Mike

Reputation: 2005

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.

Upvotes: 0

Iain
Iain

Reputation: 387

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

Upvotes: 2

piet.t
piet.t

Reputation: 11911

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.

Upvotes: 0

Related Questions