Oracle SQL - Эффективный поиск даты последнего изменения между двумя датами

Question

Я часто использую следующий запрос Oracle SQL, чтобы найти эффективное изменение последней даты между двумя датами, но оно не очень эффективно (полное сканирование таблицы). Первичный ключ на per_all_people_f является person_id, effective_start_date, effective_end_date,

В основном для имени пользователя (которое не хранит даты, вступающие в силу изменений), я хотел бы найти детали сотрудника, который идет с этим пользователем. Изменения сотрудников, тем не менее, эффективно сохраняют дату, и поэтому мне нужно найти действительное изменение последней даты между параметрами от и до даты.

Есть ли индекс Oracle, который работает между двумя датами? Есть ли уловка, которую я могу использовать, чтобы использовать существующий индекс первичного ключа с датами от и до? Как я могу написать запрос, чтобы быть более эффективным? Почти все запросы, которые я пишу, будут использовать эту логику.

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

Если подумать, Oracle должен тратить на это много места effective_start_date а также effective_end_date в индексе первичного ключа, так как они будут использоваться только в том случае, если вы знаете дату effective_start_date,

Answer 1

Там нет необходимости присоединиться per_all_people_f дважды, попробуйте ROW_NUMBER.

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

Answer 2

Это еще один вариант:

select
fu.user_name,
papf.employee_number
from
fnd_user fu,
(
select distinct
papf.person_id,
min(papf.employee_number) keep (dense_rank last order by papf.effective_start_date) over (partition by papf.person_id) employee_number 
from
per_all_people_f papf
where
papf.effective_start_date between :p_from and :p_to
) papf
where
fu.employee_id=papf.person_id(+)

Примечание по производительности: если вы хотите перечислить всех пользователей и их соответствующие возможные изменения записей о персонале в пределах определенного диапазона дат, наилучшим выбором, вероятно, будет хеш-соединение для обоих полных наборов данных. Если у вас огромное количество сотрудников, но не у многих из них есть пользователь приложения, доступ к индексу, предложенный /*+ push_pred(papf)*/ может быть лучше. Если диапазон дат невелик и выборочен, создайте индекс для ffective_start_date, чтобы позволить оптимизатору выполнить хеш-соединение записей fnd_user и per_all_people_f, извлекаемых этим пользовательским индексом.

Чтобы судить, какая опция лучше, не смотрите на время выполнения, включите автоматическую трассировку и проверьте, какая опция имеет самый низкий IO.