条件联接性能优化 - Oracle SQL

Question

我需要查看我们数据库中的物质对象，它可以是我的小型建筑公司处理的任何类型的交易。然后，根据交易类型，确定到期日期和承诺日期。有十五种不同的交易类型，但我主要关注四五种：

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_DateType_AB_ Due_Date等。

Answer 1

您也可以尝试以下方法：

SELECT
datatable.ID_Number,
datatable.Object_Type,
COALESCE (tblPO.PO_DUE_DATE, tblPROD.PROD_DUE_DATE, tblPLAN.PLAN_DUE_DATE, 
tblPURCHASE.NEED_DUE_DATE) AS Object_Due_Date,
COALESCE (tblPO.PO_PROM_DATE, tblPROD.PROD_PROM_DATE, tblPLAN.PLAN_PROM_DATE,
NEED_PROM_DATE.NEED_DUE_DATE) AS Object_Promised_Date
FROM
datatable
LEFT JOIN tblPO 
ON  datatable.ID_Number = tblPO.PO_ID
AND datatable.Object_Type = 'AA'
LEFT JOIN tblPROD 
ON  datatable.ID_Number = tblPROD.PROD_ID
AND datatable.Object_Type = 'AB'
LEFT JOIN tblPLAN 
ON  datatable.ID_Number = tblPLAN.PLAN_ID
AND datatable.Object_Type = 'AC'
LEFT JOIN tblPURCHASE
ON  datatable.ID_Number = tblPURCHASE.PURCHASE_ID
AND datatable.Object_Type = 'BN'
WHERE
( other filtering criteria )

Answer 2

试试这个

SELECT
datatable.ID_Number,
datatable.Object_Type,
tblPO.PO_DUE_DATE Object_Due_Date,
tblPO.PO_PROM_DATE Object_Promised_Date
FROM
datatable
join tblPO on  datatable.ID_Number = tblPO.PO_ID
WHERE
Object_Type = 'AA' 
( other filtering criteria )
union all
SELECT
datatable.ID_Number,
datatable.Object_Type,
tblPROD.PROD_DUE_DATE, 
tblPROD.PROD_PROM_DATE 
FROM
datatable
join tblPLAN on  datatable.ID_Number = tblPLAN.PLAN_ID
WHERE
Object_Type = 'AB' 
( other filtering criteria )
union all 
as early with tables tblPLAN and  tblPURCHASE 

Answer 3

这样的事情怎么样？

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 )

我只是用LEFT JOINs替换了您的条件SELECTs. 您可以对此查询与原始查询运行EXPLAIN PLAN，看看这是否改变了任何内容。

其他想法

• 如果您还没有在datatable.ID_Number上定义索引，则应在其上创建一个索引，因为它在所有SELECTs/JOINs中都可以访问

• 可能会在其他JOIN列(PO_ID、PROD_ID等(上创建索引(如果它们尚未编制索引(

• 如果您知道行将始终存在于LEFT JOIN表中，请将联接更改为INNER JOIN...这可能会加快速度

Answer 4

与 Needle 的答案类似，但我喜欢将来自不同来源的类似数据组合到一个视图中。在这种情况下，我使用的是内联视图，但是如果您要在很多地方使用它，那么创建一个实际视图以仅显示不同事务类型的到期和承诺日期可能是值得的。

SELECT
datatable.ID_Number,
datatable.Object_Type,
type_lookups.Object_Due_Date,
type_lookups.Object_Promised_Date
FROM
datatable
LEFT JOIN (
select 'AA' as Object_Type,
PO_DUE_DATE as Object_Due_Date,
PO_PROM_DATE as Object_Promised_Date,
PO_ID as ID
from tblPO
union all
select 'AB' as Object_Type,
PROD_DUE_DATE as Object_Due_Date,
PROD_PROM_DATE as Object_Promised_Date,
PROD_ID as ID
from tblPROD 
union all
select 'AC' as Object_Type,
PLAN_DUE_DATE as Object_Due_Date,
PLAN_PROM_DATE as Object_Promised_Date,
PLAN_ID as ID
from tblPLAN
union all
select 'BN' as Object_Type,
NEED_DUE_DATE as Object_Due_Date,
NEED_PROM_DATE as Object_Promised_Date,
PURCHASE_ID as ID
from tblPURCHASE
) type_lookups
ON type_lookups.object_type = datatable.Object_Type
AND type_lookups.ID = datatable.ID_Number
WHERE
( other filtering criteria )