Hello ,
I am working on query with bind variables and joins . In below code I have used multiple OR operators and what I can see is if I keep any of these OR clause query runs faster but once I start adding other OR operator it takes too much time (200-500) sec
select person_id from(
SELECT
perpeopleextrainfoeo.person_id as person_id, max(PerPeopleExtraInfoEO.PEI_INFORMATION3) as pei
FROM PER_PEOPLE_EXTRA_INFO PerPeopleExtraInfoEO
where PerPeopleExtraInfoEO.PEI_INFORMATION_CATEGORY = 'MCD_HC_UK_NIGHT_WORKER'
and PerPeopleExtraInfoEO.PEI_INFORMATION3 is not null
group by perpeopleextrainfoeo.person_id)nwe
WHERE
('overdue'=:inStatusReview and 'nightWorker'=:inReviewType
and pei is not null and Fnd_Date.Canonical_To_Date(nwe.pei)<sysdate)
or ('pending'=:inStatusReview and 'nightWorker'=:inReviewType
and pei is not null and Fnd_Date.Canonical_To_Date(nwe.pei)>sysdate and Fnd_Date.Canonical_To_Date(nwe.pei)<=sysdate+14)
or ('currMonth'=:inStatusReview and 'nightWorker'=:inReviewType
and pei is not null
and MONTHS_BETWEEN(Fnd_Date.Canonical_To_Date(nwe.pei),trunc(sysdate,'MM'))>0
and MONTHS_BETWEEN(Fnd_Date.Canonical_To_Date(nwe.pei),trunc(sysdate,'MM'))<1)
or
('nextMonth'=:inStatusReview and 'nightWorker'=:inReviewType and
pei is not null and
MONTHS_BETWEEN(Fnd_Date.Canonical_To_Date(nwe.pei),trunc(sysdate,'MM'))>1 and MONTHS_BETWEEN(Fnd_Date.Canonical_To_Date(nwe.pei),trunc
(sysdate,'MM'))<2) )Nightworker On (Newteam.Person_Id=Nightworker.Person_Id And 'nightWorker'=:inReviewType)