Оптимизация запроса SELECT, который работает медленно в Oracle, который быстро запускается на SQL Server

Ответ 1

Часто этот тип проблемы уходит, если вы анализируете вовлеченные таблицы (поэтому Oracle лучше понимает распределение данных)

ANALYZE TABLE tasks COMPUTE STATISTICS;

Ответ 2

Предложение "IN" - известно в Oracle довольно медленно. На самом деле, внутренний оптимизатор запросов в Oracle не может обрабатывать утверждения с "IN" довольно хорошо. попробуйте использовать "EXISTS":

SELECT orderID FROM tasks WHERE orderID NOT EXISTS 
    (SELECT DISTINCT orderID FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL)`print("code sample");`

Предупреждение. Пожалуйста, проверьте, не вызвал ли запрос тот же результат.

Эдит говорит: ooops, запрос не совсем сформирован, но общая идея верна. Oracle должен выполнить полное сканирование таблицы для второго (внутреннего) запроса, собрать результаты, а затем сравнить их с первым (внешним) запросом, поэтому он замедляется. Попробуйте

SELECT orderID AS oid FROM tasks WHERE NOT EXISTS 
    (SELECT DISTINCT orderID AS oid2 FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL and oid=oid2)

или что-то похожее; -)

Ответ 3

Я бы попытался использовать соединения вместо

SELECT 
    t.orderID 
FROM 
    tasks  t
    LEFT JOIN tasks t1
        ON t.orderID =  t1.orderID
        AND t1.engineer1 IS NOT NULL 
        AND t1.engineer2 IS NOT NULL
WHERE
    t1.orderID IS NULL 

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

SELECT orderID FROM orders WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

(при условии, что у вас есть таблица заказов со всеми указанными заказами)

который затем можно переписать с помощью объединений как:

SELECT 
    o.orderID 
FROM 
    orders o
    LEFT JOIN tasks t
        ON o.orderID =  t.orderID
        AND t.engineer1 IS NOT NULL 
        AND t.engineer2 IS NOT NULL
WHERE
    t.orderID IS NULL 

Ответ 4

Некоторые вопросы:

• Сколько строк в задачах?
• Какие индексы определены на нем?
• Недавно была проанализирована таблица?

Другой способ написать тот же запрос:

select orderid from tasks
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Однако я предпочел бы, чтобы запрос включал таблицу "orders":

select orderid from ORDERS
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

или

select orderid from ORDERS
where orderid not in
( select orderid from tasks
  where engineer1 IS NOT NULL AND engineer2 IS NOT NULL
)

или

select orderid from ORDERS
where not exists
( select null from tasks
  where tasks.orderid = orders.orderid
  and   engineer1 IS NOT NULL OR engineer2 IS NOT NULL
)

Ответ 5

Я согласен с TZQTZIO, я не получаю ваш запрос.

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

Название вопроса не очень полезно. Я мог бы установить этот запрос в одной базе данных Oracle и сделать его медленным и ускорить его работу в другом. Существует множество факторов, которые определяют, как база данных разрешает запрос, статистику объектов, статистику схемы SYS и параметры, а также производительность сервера. Sqlserver и Oracle не проблема.

Для тех, кто интересуется настройкой и производительностью запросов, и хотите узнать больше, некоторые из терминов google для поиска: "oak table oracle" и "oracle jonathan lewis".

Ответ 6

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

SELECT t1.orderID 
FROM   tasks t1
WHERE  NOT EXISTS
       (SELECT 1 
        FROM   tasks t2 
        WHERE  t2.orderID   = t1.orderID
        AND    t2.engineer1 IS NOT NULL 
        AND    t2.engineer2 IS NOT NULL)

Ответ 7

"Хотя мне все еще интересно, почему я должен это делать, и если/когда мне нужно будет запустить его снова?"

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

В небольшой базе данных вы можете просто настроить работу для сбора статистики каждую ночь и оставить ее в покое. Фактически, это значение по умолчанию менее 10g. Для больших реализаций вам обычно приходится взвешивать стабильность планов выполнения против изменения данных, что является сложным балансом.

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

Ответ 8

Не ваш запрос такой же, как

SELECT orderID FROM tasks
WHERE engineer1 IS NOT NULL OR engineer2 IS NOT NULL

?

Ответ 9

Как насчет:

SELECT DISTINCT orderID FROM tasks t1 WHERE NOT EXISTS (SELECT * FROM tasks t2 WHERE t2.orderID=t1.orderID AND (engineer1 IS NOT NULL OR engineer2 IS NOT NULL));

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

Ответ 10

Другой вариант - использовать MINUS (EXCEPT на MSSQL)

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL 
AND engineer2 IS NOT NULL

Ответ 11

Если вы решили создать таблицу ORDERS, я бы добавил к ней флаг ALLOCATED и создал индекс bitmap. Этот подход также заставляет вас изменить бизнес-логику, чтобы обновить флаг, но запросы будут молниеносно. Это зависит от того, насколько важны запросы для приложения.

Что касается ответов, тем проще в этом случае. Забудьте о подзапросах, объединениях, отдельных и групповых байтах, они вообще не нужны!

Ответ 12

Какая доля строк в таблице соответствует условию "engineer1 IS NOT NULL AND engineer2 IS NOT NULL"?

Это говорит вам (примерно), стоит ли пытаться использовать индекс для получения связанных заказов.

Еще один способ написать запрос в Oracle, который будет обрабатывать неиндексированные случаи, будет:

select distinct orderid
from
(
select orderid,
       max(case when engineer1 is null and engineer2 is null then 0 else 1)
          over (partition by orderid)
          as max_null_finder
from   tasks
)
where max_null_finder = 0

Ответ 13

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

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Ответ 14

Новый вариант.

Ифф

• Функция COUNT() не учитывает значения NULL

и

• Вам нужен идентификатор orderID всех задач, в которых none задач имеют либо инженер1, либо инженер2, установленный в значение

, тогда это должно делать то, что вы хотите:

SELECT orderID
FROM tasks
GROUP BY orderID
HAVING COUNT(engineer1) = 0 AND COUNT(engineer2) = 0

Пожалуйста, проверьте его.

Ответ 15

Я согласен с TΖΩΤΖΙΟΥ и wearejimbo, что ваш запрос должен быть...

SELECT DISTINCT orderID FROM Tasks 
WHERE Engineer1 IS NULL OR Engineer2 IS NULL;

Я не знаю о SQL Server, но этот запрос не сможет использовать любые индексы, потому что нулевые строки не являются индексами. Решением этого было бы перезаписать запрос таким образом, чтобы можно было создать индекс на основе функций, который включает только строки с нулевым значением. Это можно сделать с помощью NVL2, но, скорее всего, не будет переноситься на SQL Server.

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

Ответ 16

Вот альтернативный подход, который, я думаю, дает то, что вы хотите:

SELECT orderID
 FROM tasks
 GROUP BY orderID
 HAVING COUNT(engineer1) = 0 OR COUNT(engineer2) = 0

Я не уверен, хотите ли вы "AND" или "OR" в предложении HAVING. Похоже, что согласно бизнес-логике эти два поля должны быть либо заполнены, либо оба должны быть NULL; если это будет гарантировано, вы можете уменьшить это условие до проверки инженера.

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

Ответ 17

Если у вас нет индекса по столбцам Engineer1 и Engineer2, вы всегда будете генерировать сканирование таблиц в SQL Server и эквивалент того, что может быть в Oracle.

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

SELECT DISTINCT orderID 
FROM tasks 
WHERE (engineer1 IS NULL OR engineer2 IS NULL)

Ответ 18

Подзапросы "плохо" с Oracle. Обычно лучше использовать соединения.

Вот статья о том, как переписать свои подзапросы с присоединением: http://www.dba-oracle.com/sql/t_rewrite_subqueries_performance.htm