I am using the following Oracle SQL query frequently to find the last date effective change between two dates but it is not very efficient (full table scans). The primary key on per_all_people_f is person_id, effective_start_date, effective_end_date.
Basically for a user name (which does not store date effective changes) I would like to find the detail of the employee which goes with that user. Employee changes are however stored date effectively and therefore I need to find the last date effective change between the from and to date parameters.
Is there an Oracle index that works with between two dates? Is there a trick I can use to use the existing primary key index with the from and to dates? How can I write the query to be more efficient? Almost all queries I write will use this logic.
select fu.user_name, papf.employee_number
from fnd_user fu
left outer join
(
select papf2.person_id,
max(papf2.effective_start_date) max_effective_start_date
from per_all_people_f papf2
where papf2.effective_start_date between :P_FROM and :P_TO
group by papf2.person_id
) papf3
on papf3.person_id = fu.employee_id
left outer join per_all_people_f papf
on papf.person_id = fu.employee_id
and papf.effective_start_date = papf3.max_effective_start_date
Come to think of it Oracle must be wasting a lot of disk space on that effective_start_date and effective_end_date in the primary key index, since the only time they would be used is if you know a date for effective_start_date.
There's no need to join per_all_people_f twice, try ROW_NUMBER instead.
select fu.user_name, papf3.employee_number
from fnd_user fu
left outer join
(
select papf2.person_id, papf2.employee_number,
row_number() -- latest date first
over (partition by papf2.person_id
order by effective_start_date desc ) as rn
from per_all_people_f papf2
where papf2.effective_start_date between :P_FROM and :P_TO
) papf3
on papf3.person_id = fu.employee_id
and papf3.rn = 1