Условия OR Oracle делают запрос очень медленным

У меня есть таблица (50 миллионов строк), в которой есть индексы по column_a и column_b

когда я делаю select count(*) from table where column_a in (list_a), я сразу получаю результаты.

То же самое с select count(*) from table where column_b in (list_b).

Но, когда я делаю

select count(*) from table where column_a in (list_a) or column_b in (list_b)

Мои запросы становятся безумно медленными и последние полчаса перед выводом правильного числа ... Я что-то делаю не так? Как я могу оптимизировать фактическое поведение этого запроса?

Спасибо!

План запроса 1:

Plan hash value: 2471097773


-------------------------------------------------------------
| Id  | Operation                    | Name                 |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |
|   1 |  SORT AGGREGATE              |                      |
|   2 |   NESTED LOOPS               |                      |
|   3 |    SORT UNIQUE               |                      |
|   4 |     TABLE ACCESS FULL        | LIST_A               |
|   5 |    BITMAP CONVERSION COUNT   |                      |
|   6 |     BITMAP INDEX SINGLE VALUE| MY_TABLE_IX02        |
-------------------------------------------------------------

План запроса 2

Plan hash value: 1870911518

-------------------------------------------------------------
| Id  | Operation                    | Name                 |
-------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |
|   1 |  SORT AGGREGATE              |                      |
|   2 |   NESTED LOOPS               |                      |
|   3 |    SORT UNIQUE               |                      |
|   4 |     TABLE ACCESS FULL        | LIST_B               |
|   5 |    BITMAP CONVERSION COUNT   |                      |
|   6 |     BITMAP INDEX SINGLE VALUE| MY_TABLE_IX05        |
-------------------------------------------------------------

План запроса 3:

Plan hash value: 1821967683

----------------------------------------------------------------
| Id  | Operation                       | Name                 |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |
|   1 |  SORT AGGREGATE                 |                      |
|   2 |   FILTER                        |                      |
|   3 |    VIEW                         | index$_join$_001     |
|   4 |     HASH JOIN                   |                      |
|   5 |      BITMAP CONVERSION TO ROWIDS|                      |
|   6 |       BITMAP INDEX FULL SCAN    | MY_TABLE_IX02        |
|   7 |      BITMAP CONVERSION TO ROWIDS|                      |
|   8 |       BITMAP INDEX FULL SCAN    | MY_TABLE_IX05        |
|   9 |    TABLE ACCESS FULL            | LIST_A               |
|  10 |    TABLE ACCESS FULL            | LIST_B               |
----------------------------------------------------------------

sql oracle
person Stephane    schedule 26.02.2015    source источник
comment
Что такое table? Что такое column_a и list_a? Какие у вас индексы на list_a? И Т. Д.   -  person Patrick Hofman    schedule 26.02.2015
comment
@PatrickHofman, column_a и b - это varchar, list_a - это 100 значений из выбранного в другой таблице. list_a, list_b имеют бинарные индексы, такие же, как column_a, column_b   -  person Stephane    schedule 26.02.2015
comment
Обратите внимание, что это разные подсчеты, если истинны и a, и b ...   -  person jarlh    schedule 26.02.2015
comment
@jarlh - да, я это знаю. Это сделано специально, поэтому я не получаю дубликатов, если строка проверяет оба условия. Проблема в том, что третий запрос безумно медленный.   -  person Stephane    schedule 26.02.2015
comment
Вы пробовали EXPLAIN PLAN? Oracle использует ваши индексы? Вы пробовали добавить комбинированный индекс для column_a, column_b?   -  person Frank Schmitt    schedule 26.02.2015
comment
попробуйте создать составной индекс для column_a и column_b.   -  person ibre5041    schedule 26.02.2015
comment
@FrankSchmitt - Я только что отредактировал вопрос с фактическим планом объяснения для каждого запроса   -  person Stephane    schedule 26.02.2015
comment
Это выглядит подозрительно - BITMAP INDEX SINGLE VALUE vs BITMAP INDEX FULL SCAN. Вы использовали одно и то же предложение IN для обоих тестов?   -  person Frank Schmitt    schedule 26.02.2015
comment
Я могу сказать вам по собственному опыту, что ИЛИ - это то, чего вы хотите избежать любой ценой при работе с Oracle. Насколько быстро по сравнению с «подсчетом в» + «подсчетом в б» - «подсчетом в а и в б»? Это три запроса, но это могло бы быть намного быстрее.   -  person Sam    schedule 26.02.2015
comment
Это обсуждение вашей темы выглядит интересным.   -  person Szymon Roziewski    schedule 26.02.2015
comment
@FrankSchmitt то же самое в запросе. Сэму, мне нравится идея - я думаю, что она должна ускорить работу по подсчету. Суть в том, что мне действительно нужно получить данные. count должен был проиллюстрировать мою точку зрения.   -  person Stephane    schedule 26.02.2015
comment
В этом случае вы можете попробовать и посмотреть, превосходит ли UNION операционную.   -  person Sam    schedule 26.02.2015
comment
Вы пытались заставить Oracle использовать индексы с подсказкой запроса? По моему опыту, Oracle имеет ОЧЕНЬ плохую привычку переключаться на полное сканирование таблиц. В настройках Oracle по умолчанию обычно неверно взвешиваются затраты на извлечение индекса и сканирование таблицы.   -  person Necreaux    schedule 26.02.2015
comment
@Necreaux - я не пробовал - как это заставить?   -  person Stephane    schedule 26.02.2015
comment
Вы можете сделать это с помощью подсказок запроса на разовой основе. Обычно я предпочитаю сначала возиться с настройкой optimizer_index_cost_adj в моем сеансе, поскольку синтаксис намного проще. Некоторым это может показаться спорным, но этот параметр по умолчанию обычно не подходит: dba-oracle.com/oracle_tips_cost_adj .htm   -  person Necreaux    schedule 26.02.2015
comment
Я не понимаю, почему Oracle не сделал бы следующее (мало знаю о технологии): для первого предиката OR используйте индекс и выведите ROW_ID в словарь. Для второго предиката OR используйте индекс и вставьте каждый ROW_ID в ранее созданный словарь. Используйте словарь, чтобы отфильтровать таблицу по ROW_ID, чтобы сохранить и вывести результат.   -  person Stephane    schedule 26.02.2015
comment
Все дело в стоимости. Он сравнивает стоимость и стоимость полного сканирования таблицы и решает, что полное сканирование таблицы проще / лучше. Хотя иногда это неправильно.   -  person Necreaux    schedule 26.02.2015

Ответы (2)


arrow_upward
8
arrow_downward

По моему опыту, OR имеет тенденцию оказывать негативное влияние на запросы (например, игнорировать индексы и запускать полное сканирование таблицы). Иногда это не так уж и плохо, но из-за этого у меня были запросы, которые из-за этого быстро менялись на минуты.

Одно из возможных решений - изменить OR на UNION или даже UNION ALL. В прошлом мне удавалось повысить производительность запросов, но вам придется сравнивать их друг с другом, чтобы увидеть, сработает ли это для вас.

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

Исходный запрос (отредактирован для возврата строк, поскольку вы упомянули возвращение данных вместо подсчета):

select * from table where column_a in (list_a) or column_b in (list_b)

Запрос, избегающий OR:

select * from table where column_a in (list_a)
UNION
select * from table where column_b in (list_b)

А поскольку UNION запускает DISTINCT, это тоже стоит попробовать:

select * from table where column_a in (list_a) and not column_b in (list_b)
UNION ALL
select * from table where column_b in (list_b) and not column_a in (list_a)
UNION ALL
select * from table where column_a in (list_a) and column_b in (list_b)
person Sam    schedule 26.02.2015
comment
Мне действительно нравится, что за этим стоит мысль. Я собираюсь поэкспериментировать и дам вам знать, сработало ли это - person Stephane; 26.02.2015
comment
Просто чтобы привести некоторые цифры - я перешел с 20 минут на полное сканирование до 5 минут с третьим оптимизированным запросом на объединение всех. Большое спасибо! - person Stephane; 26.02.2015

arrow_upward
1
arrow_downward

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

  • индекс по column_a предоставит Oracle информацию о значениях column_a
  • индекс по column_b предоставит Oracle информацию о значениях column_b

Но ни один из индексов не даст Oracle информации о комбинации (column_a, column_b). Итак, чтобы ускорить ваш запрос, вам понадобится индекс, который включает как column_a, так и column_b.

person Frank Schmitt    schedule 26.02.2015
comment
Индекс с обоими столбцами не поможет с запросом ИЛИ. - person Necreaux; 26.02.2015
comment
Почему нет? По крайней мере INDEX FAST FULL SCAN можно было использовать. В вопросе не указывается длина list_a / list_b, но согласно плану exec списки должны быть очень длинными. - person ibre5041; 26.02.2015
comment
@Necreaux, имеет ли значение распределение данных в случае операционной? - person jarlh; 26.02.2015
comment
Списки не более 200 элементов - person Stephane; 26.02.2015
comment
@jarlh Теоретически да. Собственно говоря, я не уверен, но, вероятно, нет. Скажем, запрос: cola = 'A' и colb = 'Z', и есть индекс для cola, colb. Если A составляет 90% данных, тогда имеет смысл просмотреть оставшиеся значения колы и найти colb = 'Z' (хотя полное сканирование таблицы, вероятно, имеет здесь больше смысла). Теперь представьте, что это наоборот, и A составляет 1% данных. Было бы намного лучше использовать отдельный индекс для colb. В конце концов, я не думаю, что оптимизатор приспособится к этим случаям, но я не уверен на 100%. - person Necreaux; 26.02.2015