sql - 需要帮助避免在查询中多次(内部)连接使用的 View

Question

我们有一个使用多个内连接的查询现在在这里，我们在同一个 View 上多次进行内部连接。它喜欢层次结构类型加入

这会导致查询的执行时间增加。

查询

    Select field1, field2 ... few fields
    FROM 
    CUST_MART.v_CPM AS P 
    JOIN CUST_MART.V_CPM_MART_HIER AS C ON p.prod_cl = c.prod_cl 
    AND g.lvl = c.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D0 ON c.prod_cl_0 = d0.prod_cl 
    AND c.lvl_0 = d0.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D2 ON c.prod_cl_2 = d2.prod_cl 
    AND c.lvl_2 = d2.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D3 ON c.prod_cl_3 = d3.prod_cl 
    AND c.lvl_3 = d3.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D6 ON c.prod_cl_6 = d6.prod_cl 
    AND c.lvl_6 = d6.lvl 
    JOIN CUST_MART.v_CPM_upc AS V on p.skt = v.skt

此查询是否有效 -

    Select field1, field2 ... few fields
    FROM 
    CUST_MART.v_CPM AS P 
    JOIN CUST_MART.V_CPM_MART_HIER AS C ON p.prod_cl = c.prod_cl 
    AND g.lvl = c.lvl 
    JOIN CUST_MART.V_CPM_MART_CLASS AS D0 ON c.prod_cl_0 = d0.prod_cl 
    AND c.lvl_0 = d0.lvl 
    AND c.prod_cl_2 = d0.prod_cl 
    AND c.lvl_2 = d0.lvl 
    AND c.prod_cl_3 = d0.prod_cl 
    AND c.lvl_3 = d0.lvl 
    AND c.prod_cl_6 = d0.prod_cl 
    AND c.lvl_6 = d0.lvl 
    JOIN CUST_MART.v_CPM_upc AS V on p.skt = v.skt 

请求

• 我重写的查询是否有效？
• 我无法在产品上运行，所以无法使用现有数据进行测试
• 此查询需要 4 小时并且每天运行，因此需要修复此查询
• 有没有其他我可以考虑的方法

Answer 1

不，您重写的查询将不起作用。考虑这个JOIN:

JOIN CUST_MART.V_CPM_MART_CLASS D0
ON c.prod_cl_0 = d0.prod_cl AND
   c.lvl_0 = d0.lvl AND
   c.prod_cl_2 = d0.prod_cl AND
   c.lvl_2 = d0.lvl AND
   c.prod_cl_3 = d0.prod_cl AND
   c.lvl_3 = d0.lvl AND
   c.prod_cl_6 = d0.prod_cl AND
   c.lvl_6 = d0.lvl 

这仅在以下情况下匹配:

c.lvl_0 = c.lvl_1 = c.lvl_2 = c.lvl_3 = c.lvl_6

此条件不在您的原始查询中。我的猜测是，这在您的数据中永远不会成立。