Performance of NOT EXISTS and COUNT(*) = 0
511152Jul 31 2008 — edited Jul 31 2008I am facing a peculiar problem while using NOT EXISTS and COUNT(*) = 0 in a query. Following are the details.
Database Version: 10.1.0.2.0 Enterprise
Table Structure:
CREATE TABLE T
(
id number(10,0) not null,
ts timestamp not null,
d_in number(15,2) not null,
d_out number(15,2) not null
)
Description: This table stores the input and output data volume for servers over a span of time. id is a foreign key referes to the server id. The table has around 3,00,000 records.
Query: In the query I'm supposed to calculate the difference between consecutive d_in (and also d_out) values for a perticular server id.
I have tried the following two queries
Query 1:
SELECT t2.id, t2.ts, t2.d_in, t2.d_out, (t2.d_in - t1.d_in), (t2.d_out - t1.d_out)
FROM T t1, T t2
WHERE t1.id = t2.id
AND t2.ts > t1.ts
AND NOT EXISTS (SELECT t3.ts
FROM T t3
WHERE t1.id = t3.id
AND t3.ts > t1.ts
AND t3.ts < t2.ts)
Query 2:
SELECT t2.id, t2.ts, t2.d_in, t2.d_out, (t2.d_in - t1.d_in), (t2.d_out - t1.d_out)
FROM T t1, T t2
WHERE t1.id = t2.id
AND t2.ts > t1.ts
AND (SELECT COUNT(*)
FROM T t3
WHERE t1.id = t3.id
AND t3.ts > t1.ts
AND t3.ts < t2.ts) = 0
The Oracle optimizer is saying the Cost for the first query is 25505 and completed the query in 2 hr 30 mins.
And for the second query, the cost is 1387. But it is executing the query for last 7 days.
Can anybody help me in finding the reason for this behavior? Or is there a better way to achieve the same thing?