Как избежать дорогостоящего декартова произведения с помощью генератора строк

Question

Я работаю над запросом (Oracle 11g), который выполняет много манипуляций с датами. Используя генератор строк, я проверяю каждую дату в диапазоне дат для каждой записи в другой таблице. По другому запросу я знаю, что мой генератор строк должен сгенерировать 8500 дат, и эта сумма будет расти на 365 дней в году. Кроме того, таблица, которую я изучаю, содержит около 18000 записей, и ожидается, что эта таблица будет увеличиваться на несколько тысяч записей в год.

Проблема возникает при соединении генератора строк с другой таблицей, чтобы получить диапазон дат для каждой записи. SQLTuning Advisor говорит, что есть дорогой декартовый продукт, который имеет смысл, учитывая, что запрос в настоящее время может генерировать до 8500 x 18000 записей. Вот запрос в его урезанном виде, без всякой логики даты и т. Д.:

with n as (
  select level n
  from dual 
  connect by level <= 8500
 )
select t.id, t.origdate + n origdate    
from (
    select id, origdate, closeddate
    from my_table
) t
join n on origdate + n - 1 <= closeddate -- here's the problem join
order by t.id, t.origdate;

Есть ли альтернативный способ объединения этих двух таблиц без декартового произведения?

Мне нужно рассчитать истекшее время для каждой из этих записей, запретив выходные и федеральные праздники, чтобы я мог отсортировать по истекшему времени. Кроме того, разбиение на страницы для таблицы выполняется на стороне сервера, поэтому мы не можем просто загрузить таблицу и отсортировать на стороне клиента.

Максимальный возраст записи в системе сейчас составляет 3656 дней, а средний - 560, так что это не так плохо, как 8500 x 18000; но все равно плохо.

Я почти смирился с тем, что добавил поле для хранения опен-дней, вычислил его один раз и сохранил истекшее время, а также создал запланированное задание для обновления всех открытых записей каждую ночь.

Answer 1

Я думаю, что вы получите лучшую производительность, если немного переписать условие соединения:

with n as (
  select level n
  from dual 
  connect by level <= 8500
 )
select t.id, t.origdate + n origdate    
from (
    select id, origdate, closeddate
    from my_table
) t
join n on Closeddate - Origdate + 1 <= n --you could even create a function-based index
order by t.id, t.origdate;