Hello Arno Brinkman,


|>PLAN returned by the server

SQL:

select count(*)
FROM "Client" WHERE "Client"."Client No" in
(
SELECT DISTINCT "Booking"."Client No" FROM "Booking"
WHERE "Booking"."End Date" >= '02-feb-2001' AND
"Booking"."Start Date" <= '02-may-2001'
)
Plan:
PLAN (Booking INDEX (IDX_Booking3,IDX_Booking))
PLAN (Client NATURAL)

Adapted plan:
PLAN (Booking INDEX (IDX_Booking3,IDX_Booking))
PLAN (Client NATURAL)
IDX_Booking3 index on "Booking"."Client No"
IDX_Booking : index on "Booking"."Start Date"




|> the DISTINCT is unneeded here.

there can be more than one booking for one client so
distinct reomove the duplicate rec in the sub query.

|>EXISTS

i tried Exists it works similar to in statement very
slow.

>>>>

SELECT
count(*)
FROM
"Client"
WHERE
EXISTS(SELECT 1 FROM "Booking"
WHERE
"Booking"."End Date" >= '02-feb-2001' AND
"Booking"."End Date" <= '02-may-2001' AND
"Booking"."Start Date" >= '02-feb-2001' AND
"Booking"."Start Date" <= '02-may-2001' AND
"Client"."Client No" = "Booking"."Client No")

End Date should be >= '02-feb-2001' and it is
not necessary that it should be <= '02-may-2001'.

i dont know, why adding index slows down the sql .
it doesn't takes the index on end date.

Thanks
Anand







___________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html