Как объединить две таблицы на основе временной метки (с отклонением в несколько секунд)?
У меня есть две таблицы, которые я пытаюсь объединить и вставить в другую таблицу на основе комбинации трех столбцов. Я объясню.
Стол М
| ANO | BNO | Timestamp | Duration
---------------------------------------------------------------
| 5612853 | 4732621 | 21.11.2013 09:50:58 | 196
| 4842988 | 5610953 | 21.11.2013 17:34:58 | 98
| 7765759 | 5612853 | 21.11.2013 20:48:00 | 377
| 2470321 | 2470263 | 21.11.2013 21:47:18 | 125
Таблица N
| ANO | BNO | Timestamp | Duration
---------------------------------------------------------------
| 5612853 | 4732621 | 21.11.2013 09:50:52 | 196
| 4842988 | 5610953 | 21.11.2013 17:34:53 | 98
| 7765759 | 5612853 | 21.11.2013 20:47:55 | 377
| 2470321 | 2470263 | 21.11.2013 21:47:13 | 125
Теперь эти две таблицы должны быть сопоставлены и вставлены в таблицу MN на основе
M.ANO=N.ANO and M.BNO=N.BNO and ((M.TIMESTAMP = N.TIMESTAMP+5/86400) or (M.TIMESTAMP = N.TIMESTAMP+6/86400))
Таким образом, в теории, мой вывод таблицы MN должен быть
| ANO | BNO | Timestamp | Duration || ANO | BNO | Timestamp | Duration
--------------------------------------------------------------------------------------------------------------
| 5612853 | 4732621 | 21.11.2013 09:50:58 | 196 || 5612853 | 4732621 | 21.11.2013 09:50:52 | 196
| 4842988 | 5610953 | 21.11.2013 17:34:58 | 98 || 4842988 | 5610953 | 21.11.2013 17:34:53 | 98
| 7765759 | 5612853 | 21.11.2013 20:48:00 | 377 || 7765759 | 5612853 | 21.11.2013 20:47:55 | 377
| 2470321 | 2470263 | 21.11.2013 21:47:18 | 125 || 2470321 | 2470263 | 21.11.2013 21:47:13 | 125
Таблица M содержит около 1,4 миллиона записей, а таблица N - около 0,9 миллиона.
Я попытался объединить две таблицы на основе следующих двух запросов. Но на это уйдут часы, и это неосуществимо, если мне придется выполнять это ежедневно.
INSERT INTO MN_RECON (
SELECT M.*,N.* FROM M FULL OUTER JOIN N ON
M.ANO=N.ANO AND M.BNO=N.BNO AND
((M.TIMESTAMP=N.TIMESTAMP+5/86400) OR (M.TIMESTAMP=N.TIMESTAMP+6/86400))
INSERT INTO MN_RECON (
SELECT M.*,N.* FROM M FULL OUTER JOIN N ON
M.ANO=N.ANO AND M.BNO=N.BNO AND
(M.TIMESTAMP-N.TIMESTAMP IN (5/86400,6/86400)
Когда я запускаю только оператор SELECT из двух вышеупомянутых запросов, я получаю вывод в течение минуты (всего несколько сотен строк выборки), но с добавленной INSERT это занимает очень много времени. Есть ли способ оптимизировать то, что я хочу сделать?
Мне нужно, чтобы он совпадал с отметкой времени, потому что в течение дня может быть несколько вхождений одной и той же комбинации ANO - BNO, причем отметка времени является уникальным идентификатором между ними.
И мне нужно полное внешнее объединение, потому что мне нужно сосредоточиться на записях, которые не совпадают, а также на сопоставленных записях с разницей в продолжительности между двумя таблицами.
Дополнительная информация Oracle 64-разрядная версия Oracle Database 11g Enterprise Edition 11.2.0.3.0
ОБЪЯСНИТЬ ПЛАН
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 2386K| 530M| | 2395M (1)|999:59:59 |
| 1 | LOAD TABLE CONVENTIONAL | MN_RECON | | | | | |
| 2 | VIEW | | 2386K| 530M| | 2395M (1)|999:59:59 |
| 3 | UNION-ALL | | | | | | |
|* 4 | HASH JOIN RIGHT OUTER| | 1417K| 109M| 49M| 10143 (1)| 00:02:02 |
| 5 | TABLE ACCESS FULL | N_VOICE | 968K| 38M| | 1753 (1)| 00:00:22 |
| 6 | TABLE ACCESS FULL | M_VOICE | 1417K| 52M| | 2479 (1)| 00:00:30 |
|* 7 | FILTER | | | | | | |
| 8 | TABLE ACCESS FULL | N_VOICE | 968K| 38M| | 1754 (1)| 00:00:22 |
|* 9 | TABLE ACCESS FULL | M_VOICE | 1 | 29 | | 2479 (1)| 00:00:30 |
1 ответ
Простой способ ускорить запрос - создать индекс на основе функций:
CREATE INDEX indexname1 ON N (timestamp+5/86400);
CREATE INDEX indexname2 ON N (timestamp+6/86400);