如何基于时间戳（具有几秒钟的差异）联接两个表？

一尘不染

如何基于时间戳（具有几秒钟的差异）联接两个表？

sql

我有两个要结合并插入到基于三列组合的另一个表中的表。我会解释。

表M

| 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 大约有140万条记录，表N 大约有90万条记录。

我已尝试根据以下两个查询将两个表连接起来。但是执行需要花费数小时，如果我必须每天运行一次，这是不可行的。

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)

当我只运行上述2个查询的SELECT语句时，在一分钟内（仅几百个示例行）就得到了输出，但是添加了INSERT会花费很长时间。有没有一种方法可以优化我想要做的事情？

我需要它与时间戳进行匹配，因为白天可能会多次出现相同的ANO-BNO组合，并且时间戳是它们之间的唯一标识符

我需要一个完整的外部联接，因为我需要专注于不匹配的记录以及两个表之间的持续时间不同的匹配记录。

其他Oracle信息 Oracle数据库11g企业版11.2.0.3.0 64位生产

解释计划

| 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 |

阅读 297

2021-03-08

共1个答案

一尘不染

一种加快查询速度的简单方法是创建一个基于函数的索引：

CREATE INDEX indexname1 ON N (timestamp+5/86400);
CREATE INDEX indexname2 ON N (timestamp+6/86400);

2021-03-08