如何优化接下来的oracle查询？

Question

我有2张桌子。表“Accs”包含 900 万行（3 列：acc_id、月份、年份）。 首先我需要提取包含部分帐号条目的记录， 然后在这些记录中查找完整匹配，如果没有——则先查找部分匹配，

WITH t AS (
  SELECT a.acc_id,
         t1.as,
         t1.cust,
         t1.curr,
         t1.code,
         t1.depart,
         t1.sdate,
         t1.stype,
         t1.amount,
         t1.s_id
  FROM table1 t1 
  LEFT JOIN Accs a 
  ON SUBSTR(a.acc_id,7,12)=t1.curr||LPAD(t1.code,4,'0')||LPAD(t1.depart,3,'0')
  WHERE t1.sdate='20.11.2011' AND t1.stype='A' AND a.month=11 ANd a.year=2011)
SELECT MAX(t.s_id),
       (CASE WHEN t.as='000000' 
             THEN (CASE WHEN ac2.acc_id IS NOT NULL THEN ac2.acc_id ELSE t.acc_id END) 
             ELSE t.cust||t.curr||LPAD(t.code,4,'0')||LPAD(t.depart,3,'0') END) acc_id
FROM t 
LEFT JOIN (SELECT t.acc_id FROM t) ac2 
ON SUBSTR(ac2.acc_id,1,6)='000'||LPAD(t.depart,3,'0')
GROUP BY      
       (CASE WHEN t.as='000000' 
             THEN (CASE WHEN ac2.acc_id IS NOT NULL THEN ac2.acc_id ELSE t.acc_id END) 
             ELSE t.cust||t.curr||LPAD(t.code,4,'0')||LPAD(t.depart,3,'0') END)

这样查询需要很长时间。我这样做是否正确？

Answer 1

第一次尝试

如果没有实际的表结构和数据，无法真正测试它，但我做了一些小的更改，有时确实会产生很大的影响。

首先，我将with部分中的LEFT JOIN更改为INNER JOIN。由于您在 WHERE 子句中使用 a 的值，因此它无论如何都会起到 inner join 的作用，并且通常要快得多，尤其是有了这么多的数据和适当的索引。

我将内部 CASE 更改为 NVL，因为这本质上就是它的作用。不知道这样会不会加快速度。

将字符串连接从外部查询移至 with 部分。

这些只是一些微小的变化，可能会产生从没有影响到相当大的影响。至少您可以在更改表结构本身之前尝试这些，尽管无论如何这样做可能是个好主意。

WITH t AS 
(
  SELECT 
    a.acc_id,
    t1.as,
    t1.cust,
    t1.curr,
    t1.code,
    t1.depart,
    t1.sdate,
    t1.stype,
    t1.amount,
    t1.s_id,
    t1.cust || t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0') as groupfield
  FROM 
    table1 t1 
    INNER JOIN Accs a
      ON SUBSTR(a.acc_id, 7, 12) = t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
  WHERE 
    t1.sdate = '20.11.2011' AND t1.stype = 'A' AND a.month = 11 ANd a.year = 2011
)
SELECT
  MAX(t.s_id),
  (CASE WHEN t.as = '000000' THEN 
    NVL(ac2.acc_id, t.acc_id)
  ELSE
    t.groupfield
  END) acc_id
FROM 
  t 
  LEFT JOIN t ac2 on ac2 
    ON SUBSTR(ac2.acc_id, 1, 6) = '000' || LPAD(t.depart, 3, '0')
GROUP BY      
  (CASE WHEN t.as = '000000' THEN 
    NVL(ac2.acc_id, t.acc_id)
  ELSE
    t.groupfield
  END)

第二次尝试

在对您的查询进行了更多研究之后，我想知道您是否不能将其设为单个/简单查询，而不是使用with。我认为通过首先内部加入 Accs，然后在附加条件下再次加入 Accs，您确实是一个很好的方法。

  SELECT 
    MAX(t1.s_id) AS s_id,
    CASE WHEN t.as = '000000' THEN
      NVL(a2.acc_id, a.acc_id)
    ELSE
      t1.cust || t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
    END AS acc_id
  FROM 
    table1 t1 
    INNER JOIN Accs a
      ON SUBSTR(a.acc_id, 7, 12) = t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
      AND a.month = 11 AND a.year = 2011
    LEFT JOIN Accs a2
      ON SUBSTR(a2.acc_id, 7, 12) = t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0')
      AND a2.month = 11 AND a2.year = 2011
      AND SUBSTR(a2.acc_id, 1, 6) = '000' || LPAD(t1.depart, 3, '0')
  WHERE 
    t1.sdate = '20.11.2011' AND t1.stype = 'A'
  GROUP BY 
    CASE WHEN t.as = '000000' THEN
      NVL(a2.acc_id, a.acc_id)
    ELSE
      t1.cust || t1.curr || LPAD(t1.code, 4, '0') || LPAD(t1.depart, 3, '0') as groupfield
    END AS acc_id

Answer 2

您可以尝试创建基于函数的索引，如下所示：

create index xxx on accs (SUBSTR(a.acc_id,7,12));