Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance of NOT EXISTS and COUNT(*) = 0

511152Jul 31 2008 — edited Jul 31 2008
I 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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 28 2008
Added on Jul 31 2008
19 comments
2,629 views