条件联接性能优化-Oracle SQL

奇迹警告

我需要查看数据库中的重要对象，该对象可以是我的小型建筑公司要处理的任何类型的交易。然后，根据交易类型，确定到期日期和承诺日期。有15种不同的交易类型，但我主要关注的是4或5种：

SELECT
    datatable.ID_Number,
    datatable.Object_Type,
    CASE
        WHEN Object_Type = 'AA' THEN (SELECT PO_DUE_DATE FROM tblPO WHERE datatable.ID_Number = tblPO.PO_ID)
        WHEN Object_Type = 'AB' THEN (SELECT PROD_DUE_DATE FROM tblPROD WHERE datatable.ID_Number = tblPROD.PROD_ID)
        WHEN Object_Type = 'AC' THEN (SELECT PLAN_DUE_DATE FROM tblPLAN WHERE datatable.ID_Number = tblPLAN.PLAN_ID)
        WHEN Object_Type = 'BN' THEN (SELECT NEED_DUE_DATE FROM tblPURCHASE WHERE datatable.ID_Number = tblPURCHASE.PURCHASE_ID)
    ELSE TO_DATE(NULL) END AS Object_Due_Date,
    CASE
        WHEN Object_Type = 'AA' THEN (SELECT PO_PROM_DATE FROM tblPO WHERE datatable.ID_Number = tblPO.PO_ID)
        WHEN Object_Type = 'AB' THEN (SELECT PROD_PROM_DATE FROM tblPROD WHERE datatable.ID_Number = tblPROD.PROD_ID)
        WHEN Object_Type = 'AC' THEN (SELECT PLAN_PROM_DATE FROM tblPLAN WHERE datatable.ID_Number = tblPLAN.PLAN_ID)
        WHEN Object_Type = 'BN' THEN (SELECT NEED_PROM_DATE FROM tblPURCHASE WHERE datatable.ID_Number = tblPURCHASE.PURCHASE_ID)
    ELSE TO_DATE(NULL) END AS Object_Promised_Date

FROM
    datatable

WHERE
    ( other filtering criteria )

这给了我这样的输出：

| ID_Number | Object_Type | Object_Due_Date | Object_Promised_Date |
|:---------:|:-----------:|:---------------:|:--------------------:|
|     1     |      AA     |    11/26/2018   |      10/18/2018      |
|     2     |      AB     |    5/12/2018    |       3/31/2018      |
|     3     |      AA     |    6/15/2018    |       9/18/2018      |
|     4     |      AA     |    1/24/2018    |       10/2/2018      |
|     5     |      ZZ     |    10/27/2018   |       6/11/2018      |
|     7     |      BN     |    1/23/2018    |       7/2/2018       |
|     8     |      AC     |     4/3/2018    |       8/3/2018       |
|     9     |      BN     |    12/1/2018    |       8/16/2018      |
|     10    |      BN     |    1/10/2018    |       10/6/2018      |

而且效果很好！问题是datatable大约有2000万条记录，而且这些日期可能会更改，因此我需要经常（每周一次或两次）刷新报告。因为对于每条记录，我有条件地连接到另一个表，所以运行和更新需要8-9个小时。

如何提高此查询的运行效率？我知道我可以离开表格，但我不知道如何用日期来填充单个列值，具体取决于Object_Type，相对于具有n列Type_AA_Due_Date Type_AB_ Due_Date等。

馄饨

这样的事情怎么样？

SELECT
  dt.ID_Number,
  dt.Object_Type,
  CASE
    WHEN Object_Type = 'AA' THEN po.PO_DUE_DATE
    WHEN Object_Type = 'AB' THEN pd.PROD_DUE_DATE
    WHEN Object_Type = 'AC' THEN pl.PLAN_DUE_DATE
    WHEN Object_Type = 'BN' THEN pc.NEED_DUE_DATE
  ELSE TO_DATE(NULL) END AS Object_Due_Date,
  CASE
    WHEN Object_Type = 'AA' THEN po.PO_PROM_DATE
    WHEN Object_Type = 'AB' THEN pd.PROD_PROM_DATE
    WHEN Object_Type = 'AC' THEN pl.PLAN_PROM_DATE
    WHEN Object_Type = 'BN' THEN pc.NEED_PROM_DATE
  ELSE TO_DATE(NULL) END AS Object_Promised_Date
FROM
  datatable dt
LEFT JOIN tblPO po ON dt.ID_Number = po.PO_ID
LEFT JOIN tblPROD pd ON dt.ID_Number = pd.PROD_ID
LEFT JOIN tbdPLAN pl ON dt.ID_Number = pl.PLAN_ID
LEFT JOIN tblPURCHASE pc ON dt.ID_Number = pc.PURCHASE_ID
WHERE
    ( other filtering criteria )

我只是将您的条件替换SELECTs为LEFT JOINs。您可以EXPLAIN PLAN在此查询与原始查询上运行，看看是否有任何改变。

其他想法