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

Предложение «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");`

Внимание: проверьте, дает ли запрос те же результаты данных.

Эдит говорит: упс, запрос плохо сформирован, но общая идея верна. 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)

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

Разве ваш запрос не совпадает с

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

?

Как насчет :

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.

Другой вариант - использовать МИНУС (ИСКЛЮЧАЯ MSSQL)

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

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

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

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

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

Я не уверен, хотите ли вы «И» или «ИЛИ» в предложении HAVING. Похоже, что в соответствии с бизнес-логикой эти два поля должны быть либо заполнены, либо оба должны иметь значение NULL; если это гарантировано, вы можете свести условие к простой проверке Engine1.

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

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

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

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

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

Однако я бы предпочел, чтобы запрос содержал таблицу «заказов»:

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
)

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

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

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

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

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

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 

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

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

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

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

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

Я думаю, что у некоторых людей есть в значительной степени правильный 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)

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

ANALYZE TABLE tasks COMPUTE STATISTICS;

Какая доля строк в таблице удовлетворяет условию «инженер1 НЕ НУЛЬ И инженер2 НЕ НУЛЬ»?

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

Другой способ написать запрос в 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

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

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

Новый взгляд.

Iff:

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

и

• Вам нужен orderID всех задач, в которых для никто задач для Engine1 или Engine2 установлено значение

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

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

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

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

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

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

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

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

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

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

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