Oracle SQL - эффективный поиск последней даты, действующей между двумя датами

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

В основном для имени пользователя (которое не сохраняет дату вступления в силу изменений) я хотел бы найти сведения о сотруднике, который работает с этим пользователем. Изменения сотрудников, однако, эффективно хранятся в дате, и поэтому мне нужно найти последнее изменение даты между параметрами from и to 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.


sql oracle indexing oracle-ebs
person Superdooperhero    schedule 24.07.2017    source источник
comment
Я не могу понять, зачем вам LEFT OUTER JOIN, нормальное соединение должно быть в порядке   -  person Sudipta Mondal    schedule 24.07.2017
comment
Кроме того, почему вы не можете получить employee_number из papf2, почему вы выбираете его из papf   -  person Sudipta Mondal    schedule 24.07.2017
comment
Не всем пользователям назначены сотрудники, отсюда и левое внешнее соединение. В качестве примера выбран номер_сотрудника, лучшим примером будет полное_имя, которое меняется, когда кто-то женится.   -  person Superdooperhero    schedule 24.07.2017
comment
Еще одна неэффективность связана с левым внешним соединением и параметрами from и to, поскольку затем мне нужно обернуть весь запрос в подзапрос, чтобы параметры from и to работали (не работает с опциональностью левого внешнего соединения)   -  person Superdooperhero    schedule 24.07.2017
comment
Присоединитесь к business_group_id, и это должно незначительно повысить производительность.   -  person Sudipta Mondal    schedule 24.07.2017
comment
Соединения business_group_id обычно отключаются индексом в приложениях оракула, добавляя + 0   -  person Superdooperhero    schedule 24.07.2017

Ответы (2)


arrow_upward
1
arrow_downward

Нет необходимости присоединяться к 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
person dnoeth    schedule 24.07.2017
comment
Определенно помогает, но все еще имеет полное сканирование таблицы и на самом деле не решает мои проблемы с датой. - person Superdooperhero; 24.07.2017
comment
@Superdooperhero: если предположить, что большая часть работы заключается в поиске последней строки, индекс (или разбиение) на effective_start_date может помочь (если он достаточно избирательный). Очевидно, столбец соединения является еще одним кандидатом в обеих таблицах. - person dnoeth; 24.07.2017
comment
Можно было бы подумать, что если бы он мог использовать индекс Effective_start_date при сортировке и нахождении максимума, он бы уже сделал это, используя индекс первичного ключа person_id, Effective_start_date, Effective_end_date. - person Superdooperhero; 24.07.2017
comment
@Superdooperhero: 1-й столбец в вашем существующем ПК - person_id, как эффективно найти максимум 2-го столбца? Индекс отсортирован по person_id, effective_start_date, effective_end_date, вы ожидаете, что это будет полезно для ORDER BY effective_start_date? Кстати, это не настоящий ПК, потому что таблица выглядит как медленно изменяющееся измерение, а ПК допускает перекрытие диапазонов дат. - person dnoeth; 24.07.2017
comment
Кажется, быстрее, если я изменю его на row_number() (раздел papf2.person_id в порядке person_id, Effective_start_date desc) как rn; но это может быть просто кеширование. - person Superdooperhero; 24.07.2017
comment
@Superdooperhero: добавление person_id в ORDER BY излишне, потому что оно уже разделено им. Я сомневаюсь, что Oracle предложит лучший план, но вы можете перепроверить - person dnoeth; 24.07.2017

arrow_upward
0
arrow_downward

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

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)*/, может быть лучше. Если диапазон дат небольшой и выборочный, создайте индекс для Effective_start_date, чтобы позволить оптимизатору выполнить хэш-соединение записей fnd_user и per_all_people_f, полученных этим пользовательским индексом.

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

person Andy Haack    schedule 25.07.2017