在 WHERE 中使用“OR”时查询中的性能问题-解网

问：

此查询在 700 毫秒内执行：

SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl
  ON pcl.COL1 = ba.COL1
 AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
  ON pscl.COL2 = pcl.COL2
 AND pscl.COL4 <> 3
 AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
LEFT JOIN T6 bba
  ON bba.COL7 = ba.COL7
LEFT JOIN T7 bsa
  ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
  ON pp.COL1 = pcl.COL1
WHERE (ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)

问题是：

(ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)

COL8 和 COL9 有索引。如果我保留 3 个条件中的任何一个，那么执行需要 30 毫秒。如果我保留其中的 2 个，那么执行需要 300 毫秒。但我需要所有 3 个。如何改进/重写我的 SELECT？

SQL Oracle Oracle11g 查询优化

SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM   T1 ba
       JOIN T2 pcl
       ON    pcl.COL1 = ba.COL1
         AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
       JOIN T5 pscl
       ON     pscl.COL2 = pcl.COL2
          AND pscl.COL4 <> 3
          AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
       LEFT JOIN T8 pp
       ON pp.COL1 = pcl.COL1
WHERE  ba.COL8 = 617617
OR     EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617)
OR     EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617)

上一个：使用 EXISTS 时出现奇怪的性能问题

下一个：如何改进这个 SELECT？

在 WHERE 中使用“OR”时查询中的性能问题

Performance issues in queries when "OR" is used inside WHERE

评论