Производительность INNER JOIN с условием "<" или ">"

Question

У меня есть две таблицы с колонкой SessionOrder, Этот столбец является целочисленным типом данных и имеет следующий индекс: CREATE INDEX OSIDX_<internal name> ON <Entity>,

Я выполняю следующий запрос:

SELECT i_0.rn, i_1.rn 
FROM (
    SELECT "RawEvent"."SessionOrder" as rn
    FROM "RawEvent" i_0
    WHERE something = 12
)
INNER JOIN (
    SELECT "RawEvent"."SessionOrder" as rn
    FROM "RawEvent" i_1
    WHERE something = 14
) ON i_0.rn > i_1.rn

Проблема этого запроса заключается в ON i_0.rn > i_1.rn это становится слишком медленным и время ожидания. Я заменил его на ON i_0.rn = i_1.rn и это было очень быстро, но, очевидно, не дает ожидаемых результатов.

Кто-нибудь знает способ увеличить производительность этого запроса, избегая тайм-аута? Другая цель этого вопроса - понять, почему он плохо работает с ON i_0.rn > i_1.rn,

PS: невозможно увеличить время ожидания

Answer 1

Пожалуйста, сначала проверьте, действительно ли вы используете базу данных Oracle. Синтаксис вашего SQL предполагает использование другой СУБД или какого-либо препроцессора.

Чтобы получить представление о том, что вы можете ожидать от таких запросов, вы можете использовать фиктивный пример следующим образом.

Создать образец данных

create table myTab as 
with mySeq as 
(select rownum SessionOrder from dual connect by level <= 10000)
select 12 something, SessionOrder from mySeq union all
select 14  something, SessionOrder from mySeq
;

Это производит оба подисточника каждый с 10.000 последовательностей, начиная с 1 до 10.000.

Тестовый запрос

create table myRes as
select a.SessionOrder rn0, b.SessionOrder rn1
from myTab a join myTab b on a.SessionOrder > b.SessionOrder and
a.something = 12 and b.something = 14;

Создает 49.995.000 строк менее чем за 30 секунд.

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

Answer 2

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

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

SELECT i_0."SessionOrder",  i_1."SessionOrder"
FROM "RawEvent" i_0
INNER JOIN "RawEvent" i_1 ON  i_0."SessionOrder" < i_1."SessionOrder" 
WHERE i_0."something" = 12 AND i_1."something" = 14

Answer 3

Ваш запрос выполняет три задачи:

1) получить данные для обоих подмножеств (12 и 14)

2) объединить данные и

3) передать результат клиенту

Обратите внимание, что доступ к индексу (который, как вы подозреваете, может вызвать проблемы) важен только для шага 1. Поэтому, чтобы получить лучшее впечатление, в первую очередь нужно понять распределение времени, прошедшего между этими тремя шагами. Это можно сделать с помощью SQL*Plus (я использую те же данные, что и в предыдущем ответе)

Доступ к данным

Поскольку моя таблица не имеет индекса, выполнение count(*) выполняет ПОЛНОЕ СКАНИРОВАНИЕ. Так что в худшем случае для получения данных используется двойное время.

SQL> set timi on
SQL> set autotrace on
SQL> select count(*) from mytab;

  COUNT(*)
----------
     20000

Elapsed: 00:00:01.13

Execution Plan
----------------------------------------------------------
Plan hash value: 3284627250

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  5472   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MYTAB | 20000 |  5472   (1)| 00:00:01 |
--------------------------------------------------------------------

FTS готов примерно через одну секунду, так что получить обе группы aprox. две секунды прошло.

ПРИСОЕДИНИТЬСЯ

Истекшее время для соединения может быть смоделировано с помощью CTAS запроса на соединение.

SQL> create table myRes as
  2  select a.SessionOrder rn0, b.SessionOrder rn1
  3  from myTab a join myTab b on a.SessionOrder > b.SessionOrder and
  4  a.something = 12 and b.something = 14;

Table created.

Elapsed: 00:00:23.65

Объединение возвращает около 50 миллионов строк (из-за условия "больше") и занимает около 21 секунды (я вычитаю 2 секунды для доступа к данным).

Передать данные клиенту

Мы используем опцию set autotrace traceonly подавить вывод запроса на экран клиента, но данные передаются, чтобы мы могли измерить время. (Если вы отобразите результат на экране, время будет еще выше)

SQL> SET ARRAYSIZE 5000
SQL> set autotrace traceonly
SQL> select a.SessionOrder rn0, b.SessionOrder rn1
  2  from myTab a join myTab b on a.SessionOrder > b.SessionOrder and
  3  a.something = 12 and b.something = 14;

49995000 rows selected.

Elapsed: 00:03:03.89

Execution Plan
----------------------------------------------------------
Plan hash value: 2857240533

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    49M|   667M| 11077   (2)| 00:00:01 |
|   1 |  MERGE JOIN         |       |    49M|   667M| 11077   (2)| 00:00:01 |
|   2 |   SORT JOIN         |       | 10000 | 70000 |  5473   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| MYTAB | 10000 | 70000 |  5472   (1)| 00:00:01 |
|*  4 |   SORT JOIN         |       | 10000 | 70000 |  5473   (1)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| MYTAB | 10000 | 70000 |  5472   (1)| 00:00:01 |
-----------------------------------------------------------------------------

Вот самое время потратить около 2:40 минут

Резюме

Таким образом, в сценарии из общего количества 3 минуты + только около 2 секунд тратится на доступ к данным (или около 1%). Даже если вы сократите доступ к данным до десятой - вы не увидите практически никакой разницы. Проблема заключается в соединении и, тем более, в передаче данных клиенту.

Когда индекс может помочь

И конечно это зависит...

В очень особом случае, когда у вас есть очень большая таблица с очень маленькими данными с something in (12,14) Вы можете получить прибыль от индекса, определенного для чего-то И SessionOrder. Это позволяет использовать индексный доступ только к данным, минуя доступ к таблицам.