重写 SQL 以获得最佳性能 (oracle)

Question

我们想知道重写以下SQL的最佳方法是什么，以便它可以在Oracle数据库中更好地执行。

如您所见，查询是根据基于键的数据从两个表(期间和帐户(中筛选的。我相信这可以很好地调整，可能会用!=带来任何好处等替换<>。

SELECT 
    p.key, p.period 
FROM 
    Period p 
WHERE
    p.version = 0 
    AND p.balance <> 0 
    AND EXISTS (SELECT 1 
                FROM Period p2 
                WHERE p2.jointKey <> 0 
                  AND p.key = p2.jointKey 
                  AND p.period = p2.period 
                  AND EXISTS (SELECT 1 FROM Account a 
                              WHERE a.customerKey = :B1 AND a.key = p.jointKey) );

Answer 1

其中一个重写将如下所示

SELECT p1.key, p1.period 
FROM Period p1 
join Period p2 on p1.key = p2.jointKey AND p1.period = p2.period and p2.jointKey <> 0
join Account a on a.key = p.jointKey
WHERE p.version = 0
and a.customerKey = :B1

Answer 2

请验证表的索引并查询决定分开。

(SELECT 1 FROM Account a WHERE a.customerKey = :B1 AND a.key = p.jointKey)
(SELECT 1 FROM Period p2 WHERE p2.jointKey <> 0 AND p.key = p2.jointKey AND p.period = p2.period AND EXISTS  );

SELECT p.key, p.period FROM Period p WHERE p.version = 0 AND p.balance <> 0 AND EXISTS