时间戳

首页 > 解决方案 > 使用 2 个键加速连接

sql - 使用 2 个键加速连接

问题描述

我可以以某种方式加快速度吗？

CREATE TABLE TEST AS 
SELECT t1.Tech, t2.Coloar,t2.Car from Table1 t1 
INNER JOIN Table2 t2 on (t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI) OR (t1.RA = t2.RA) 
where...

如果我只用

(t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI)

或使用此键

(t1.RA = t2.RA)

这需要几秒钟，但都需要几分钟，我有更多更大的表要创建，有时我需要 LEFT JOIN 这个密钥对，比如

CREATE TABLE...
INNER JOIN...
LEFT JOIN on (t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI) OR (t1.RA = t2.RA)

标签： sqlpostgresqljoinnetezza

解决方案

OR从ON子句中删除：

CREATE TABLE TEST AS 
    SELECT t1.Tech,
           COALESCE(t2.Color, tt2.Color),
           COLAESCE(t2.Car, tt2.Car)
    FROM Table1 t1 LEFT JOIN
         Table2 t2 
         ON t1.ID = t2.ID AND t1.IT = t2.IT AND t1.LIFI = t2.LIFI LEFT JOIN
         Table2 tt2
         ON t1.RA = tt2.RA AND t2.ID IS NULL
     WHERE (t2.ID IS NOT NULL OR tt2.ID IS NOT NULL) AND
           ...

这应该能够为两个连接利用适当的索引。

推荐阅读