Увеличьте производительность SQL-запроса
У меня есть таблица переходов, в которой мне нужно получить дату и время переходов для 5 различных переходов (сброс заказа, подбор заказа, адрес заказа, помеченный, загруженный и отправленный).
Для каждого заказа я пытаюсь получить время этих переходов на конкретную дату и для заказов, которые мы отправляем в эту дату. Мой запрос выполняется почти для всех, чтобы получить эту информацию.
Есть ли лучший способ переписать это? Буду очень признателен за вашу помощь.
SELECT
"ORDER",
MAX(CASE WHEN "ACTION" = 'DNLD' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd
hh24:mi') END) AS ORDER_Drop_time,
MAX(CASE WHEN "ACTION" = 'REQUEST' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi') END) AS Label_request_time,
MAX(CASE WHEN "ACTION" = 'PICK' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi') END) AS pick_time,
MAX(CASE WHEN "ACTION" = 'LOAD' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi') END) AS Load_TIME,
MAX(CASE WHEN "ACTION" = 'SHIP' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi') END) AS SHIP_COM_TIME
FROM
historymaster hm
WHERE
"ORDER" IN (
SELECT
"ORDER"
FROM
historymaster
WHERE
datetimecreated >=:usestartdate
AND datetimecreated <=:useenddate
AND "ACTION" = 'SHIP'
AND "OBJECT" = 'OBORDLINE'
) -- Looking up order ID for ship transations and using it
AND (
(-- Order drop
"ACTION" = 'DNLD'
AND "OBJECT" = 'OBORDLINE'
AND actionmodifier IS NULL
AND reasoncode = '00'
)
OR --Address label request
(
"ACTION" = 'REQUEST'
AND "OBJECT" = 'LABEL'
AND "CLASS" = 'ADDR'
)
OR -- pick
(
"ACTION" = 'PICK'
AND "OBJECT" = 'OBO'
AND "CLASS" = 'INVE'
AND actualquantity != 0
AND substr(ordertype,1,1) = 'N'
)
OR -- Trailer Load
(
"ACTION" = 'LOAD'
AND "OBJECT" = 'OBO'
AND "CLASS" = 'INVE'
)
OR --Ship Complete
(
"OBJECT" = 'OBORDLINE'
AND hm.package = ' '
AND actionmodifier IS NULL
AND "ACTION" = 'SHIP'
)
)
GROUP BY
"ORDER";
Выход:
ORDER ORDER_Drop_time Label_request_time PICK_TIME Load_TIME SHIP_COM_TIME
D2KJJKJLB-35689 8/2/2018 9:50 8/6/2018 9:50 8/6/2018 8:50 8/6/2018 10:50 8/7/2018 14:16
Предоставьте больше данных: сколько ORDER вы запрашиваете, сколько ORDER и строк содержит ваша таблица. Показать использованный план выполнения
У меня 7000 заказов. объяснить план Извините, я не могу получить план объяснения в том формате, который вы просили.
Ответы 2
Вы должны использовать индексы, если этот запрос становится медленным.
Самый важный показатель, о котором я могу думать, это:
create index ix1 on historymaster ("OBJECT", "ACTION", datetimecreated);
Кроме того, если вы действительно хотите, чтобы этот запрос был еще быстрее, вы можете добавить:
create index ix2 on historymaster ("OBJECT", "ACTION", reasoncode);
create index ix3 on historymaster ("OBJECT", "ACTION", "CLASS");
Будьте осторожны, чтобы не добавлять многие индексы в наши таблицы, поскольку это может замедлить работу ваших INSERT, UPDATE и DELETE. Вам нужно найти баланс.
Получите план выполнения запроса. Сделать это:
Подготовьте извлечение:
drop table plan_table;Составьте план выполнения:
explain plan for select ...Получить план выполнения:
select plan_table_output from table(dbms_xplan.display('plan_table',null,'typical'));Как только вы его получите, добавьте план к своему вопросу.
есть ли другой способ повысить производительность, так как у меня нет доступа для добавления индекса
Чтобы ответить на ваш вопрос, вам нужно будет опубликовать полный список существующих индексов, а также первичный ключ и уникальные ограничения в этой таблице. Вы можете добавить их к вопросу?
Есть 10 индексов, и у меня уже есть индекс, у одного из индексов есть все эти столбцы. который использовался в плане объяснения (TRANS_SEQ_NUM, OID, LIST, TRANS_ACT, WHSE_ID, LNO, TRANS_CLASS, TR ANS_ACT_MOD, OID_TYPE, TYPE_OF_ORDER, TRANS _OBJ)
Я также добавил к вопросам текущий индекс в таблице истории, и в этой таблице нет ограничений.
Что ж, похоже, ваши индексы на месте. Если все еще медленно, можете ли вы опубликовать ПЛАН ОБЪЯСНЕНИЯ? В моем ответе добавлены шаги, чтобы сделать это, если вы не знаете, как это сделать.
Я добавил снимок экрана с объяснительной равниной. Я смог отделить проблему, вызывающую производительность, но не смог найти решения. Запрос выполняется в течение 30 секунд. но когда я добавляю фильтр в предложение where («Поиск идентификатора заказа для перемещений судов и его использование») для поиска идентификатора заказа для этих конкретных данных о корабле, стоимость производительности возрастает экспоненциально и занимает более 10 минут. если бы вы могли направить меня на то, что я должен искать, это было бы очень полезно.
Ваш запрос выполняет в основном два доступа, поэтому (при условии, что вы выбираете только небольшое количество заказов) вам нужны два индекса, чтобы отразить его.
Первый индекс выбирает orders с соответствующими action и object в заданном временном диапазоне.
("ACTION","OBJECT",datetimecreated)
Затем второй индекс запрашивает все записи для тех orders с запрошенными action и object.
("ORDER", "ACTION" ,"OBJECT")
Чтобы продемонстрировать принципала, я создал тестовую таблицу с 2 миллионами строк и 100 тысячами заказов (см. Сценарий ниже).
Обратите внимание, что я не использую зарезервированные имена (например, «ORDER» - это order_id), и я использую «нейтральные» имена для действий и объектов.
select order_id,
max(case when action_id = 'A1' then datetimecreated end) as A1_date,
max(case when action_id = 'A2' then datetimecreated end) as A2_date,
max(case when action_id = 'A3' then datetimecreated end) as A3_date,
max(case when action_id = 'A4' then datetimecreated end) as A4_date,
max(case when action_id = 'A5' then datetimecreated end) as A5_date
from test
where order_id in (
select order_id
from test
where datetimecreated >= DATE'2018-02-05' and datetimecreated <= DATE'2018-02-06' and
action_id = 'A1' and object_id = 'O1')
AND
(action_id = 'A1' and object_id = 'O1' or
action_id = 'A2' and object_id = 'O2' or
action_id = 'A3' and object_id = 'O3' or
action_id = 'A4' and object_id = 'O4' or
action_id = 'A5' and object_id = 'O1')
group by order_id;
ORDER_ID A1_DATE A2_DATE A3_DATE A4_DATE A5_DATE
---------- -------- -------- -------- -------- --------
826 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
833 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
.....
823 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
838 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
25 rows selected.
Elapsed: 00:00:00.10
Вы ожидаете, что этот план выполнения - внутренний NESTED LOOP выполняет два доступа, описанных выше - см. условие доступа 5 и 7.
Plan hash value: 1930696803
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 13380 | 12 (9)| 00:00:01 |
| 1 | HASH GROUP BY | | 223 | 13380 | 12 (9)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 223 | 13380 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 3 (0)| 00:00:01 |
| 6 | INLIST ITERATOR | | | | | |
|* 7 | INDEX RANGE SCAN | TEST_IDX2 | 1 | | 6 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | TEST | 502 | 15060 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ACTION_ID"='A1' AND "OBJECT_ID"='O1' AND
"DATETIMECREATED">=TO_DATE(' 2018-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"DATETIMECREATED"<=TO_DATE(' 2018-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - access("ORDER_ID" = "ORDER_ID" AND ("ACTION_ID"='A1' AND "OBJECT_ID"='O1' OR
"ACTION_ID"='A2' AND "OBJECT_ID"='O2' OR "ACTION_ID"='A3' AND "OBJECT_ID"='O3' OR
"ACTION_ID"='A4' AND "OBJECT_ID"='O4' OR "ACTION_ID"='A5' AND "OBJECT_ID"='O1'))
Вот сценарий для создания тестовых данных для игры:
create table test as
with ord as (
select rownum order_id, date'2018-01-01' + rownum / 24 datetimecreated from dual connect by level <= 100000),
act as (
select 'A1' action_id, 'O1' object_id, 1 offset from dual union all
select 'A1' action_id, 'O2' object_id, 2 offset from dual union all
select 'A1' action_id, 'O3' object_id, 3 offset from dual union all
select 'A1' action_id, 'O4' object_id, 4 offset from dual union all
select 'A2' action_id, 'O1' object_id, 5 offset from dual union all
select 'A2' action_id, 'O2' object_id, 6 offset from dual union all
select 'A2' action_id, 'O3' object_id, 7 offset from dual union all
select 'A2' action_id, 'O4' object_id, 8 offset from dual union all
select 'A3' action_id, 'O1' object_id, 9 offset from dual union all
select 'A3' action_id, 'O2' object_id, 10 offset from dual union all
select 'A3' action_id, 'O3' object_id, 11 offset from dual union all
select 'A3' action_id, 'O4' object_id, 12 offset from dual union all
select 'A4' action_id, 'O1' object_id, 13 offset from dual union all
select 'A4' action_id, 'O2' object_id, 14 offset from dual union all
select 'A4' action_id, 'O3' object_id, 15 offset from dual union all
select 'A4' action_id, 'O4' object_id, 16 offset from dual union all
select 'A5' action_id, 'O1' object_id, 17 offset from dual union all
select 'A5' action_id, 'O2' object_id, 18 offset from dual union all
select 'A5' action_id, 'O3' object_id, 19 offset from dual union all
select 'A5' action_id, 'O4' object_id, 20 offset from dual)
select order_id, datetimecreated + offset datetimecreated, action_id,object_id from ord
cross join act;
create index test_idx1 on test(action_id, object_id,datetimecreated);
create index test_idx2 on test(order_id,action_id, object_id);
спасибо, это было очень полезно. Я смог отделить проблему, вызывающую производительность, но не смог найти решения. Запрос выполняется в течение 30 секунд. но когда я добавляю фильтр в предложение where («Поиск идентификатора заказа для перемещений судов и его использование»), чтобы получить идентификатор заказа для этих конкретных данных о корабле, стоимость производительности возрастает экспоненциально и занимает более 10 минут. если бы вы могли направить меня на то, что я должен искать, это было бы очень полезно.
Производительность хороша, когда я просто использую фильтр даты, но плохо работает, когда я пытаюсь проверить каждый идентификатор заказа, который был отправлен в определенную дату, но поскольку транзакции происходят в разные даты, мне пришлось искать каждый идентификатор заказа. Есть лучший способ это сделать?