teradata中使用不存在的性能调整

Question

IE_VT是一个挥发性表，使用唯一的主索引（store_number，upc，pos_dept，pos_dept_number）创建，

现在我们的代码试图通过从各个表中选择几个列来插入VT中。

实际查询：

INSERT INTO IE_VT 
       (STORE_NUMBER,
        UPC,
        SKU_REGIONAL,
        REGION,
        ITEM_LONG_DESCRIPTION,
        POS_DESCRIPTION,
        POS_DEPT,
        POS_DEPT_NUMBER,
        ITEM_SIZE,
        ITEM_SIZE_NUMBER,
        ITEM_UOM,
        BRAND,
        REG_HIER_REF,
        ITEM_STATUS,
        CREATE_DATE,
        COMMODITY_CODE,
        NAT_UPC,
        START_DATE,
        END_DATE,
        LAST_UPDATE)
SELECT  ir.STORE_NUMBER,  
        ir.UPC,                                               
        ir.SKU_REGIONAL,                                      
        ir.REGION,                                            
        ir.ITEM_LONG_DESCRIPTION,                             
        ir.POS_DESCRIPTION, 
        coalesce(otr.POS_DEPT,'00000'),                         -- Set to default POS_DEPT if not found in ORG_TEAM_REF                                    
        a.DEPARTMENT,                                           -- Invalid POS_DEPT, not in hierarchy.  Will be null if not valid integer.                    
               
        ir.ITEM_SIZE,                                         
        ir.ITEM_SIZE_NUMBER,                                  
        ir.ITEM_UOM,                                          
        ir.BRAND,                                             
        ir.REG_HIER_REF,                                      
        ir.ITEM_STATUS,                                                        
                   
        ir.CREATE_DATE,
        ir.COMMODITY_CODE,
        ir.NAT_UPC,
        ir.START_DATE,                                   
        COALESCE(ir.END_DATE, CAST('2999-12-31' AS DATE)) END_DATE,
        ir.LAST_UPDATE
 FROM   
       (
               SELECT distinct  x.STORE_NUMBER, 
                                x.UPC, 
                                x.DEPARTMENT
               FROM             EW.PRD_SCS x
               LEFT OUTER JOIN  EW.ITRM_R y            -- Invalid POS_DEPT_NUMBER in PRD_SALES_COST_SUMMARY.
                   ON  x.STORE_NUMBER =    y.STORE_NUMBER       -- But still valid item per STORE_NUMBER / UPC in IR.
                   AND x.UPC =             y.UPC
                   and x.DEPARTMENT =      y.POS_DEPT_NUMBER 
                   and y.PDN_END_DATE is null
               WHERE y.ITEM_REGION_ID IS NULL
       ) A        
join    EDW.STORE B  
            on  a.STORE_NUMBER = b.STORE_NUMBER
            and b.END_DATE is null  
join    EW.ITRM_R ir                                   -- Item is valid for STORE.
            on  b.REGION =          ir.REGION
            and a.STORE_NUMBER =    ir.STORE_NUMBER
            and a.UPC =             ir.UPC
            and ir.END_DATE is null
LEFT OUTER JOIN                  
        EDW.ORG_TEAM_REF  otr                                -- With possibly still valid POS_DEPT_NUMBER per OTR.
            on  b.REGION =          otr.REGION
            and a.STORE_NUMBER  =   otr.STORE_NUMBER
            and a.DEPARTMENT =      otr.POS_DEPT_NUMBER
            and otr.END_DATE is null -- 2678712 records
--------            
where  (a.STORE_NUMBER, a.UPC, trim(leading '0' from coalesce(otr.POS_DEPT,'00000')), a.department)
            not in  (select STORE_NUMBER,                       -- LOJ to IE_VT is much slower.
                            UPC, 
                            trim(leading '0' from POS_DEPT),
                            POS_DEPT_NUMBER
                     from   IE_VT); -- 2678700 records

我们的实际查询运行良好，直到上面的5行标记为--------仅此之后，我们的查询消耗了500k CPU秒，并且运行左右大约10分钟，并为我们提供了最终的最终集合2678700记录。

在那里，我尝试替换在不存在的地方的地方，是更改的查询。

---

调查查询：

INSERT INTO IE_VT1 
       (STORE_NUMBER,
        UPC,
        SKU_REGIONAL,
        REGION,
        ITEM_LONG_DESCRIPTION,
        POS_DESCRIPTION,
        POS_DEPT,
        POS_DEPT_NUMBER,
        ITEM_SIZE,
        ITEM_SIZE_NUMBER,
        ITEM_UOM,
        BRAND,
        REG_HIER_REF,
        ITEM_STATUS,
        CREATE_DATE,
        COMMODITY_CODE,
        NAT_UPC,
        START_DATE,
        END_DATE,
        LAST_UPDATE)
SELECT  ir.STORE_NUMBER, 
        ir.UPC,                  
        ir.SKU_REGIONAL,                                      
        ir.REGION,                                            
        ir.ITEM_LONG_DESCRIPTION,                             
        ir.POS_DESCRIPTION, 
        coalesce(otr.POS_DEPT,'00000'),                         -- Set to default POS_DEPT if not found in ORG_TEAM_REF                                    
        a.DEPARTMENT,                                           -- Invalid POS_DEPT, not in hierarchy.  Will be null if not valid integer.                    
               
        ir.ITEM_SIZE,                                         
        ir.ITEM_SIZE_NUMBER,                                  
        ir.ITEM_UOM,                                          
        ir.BRAND,                                             
        ir.REG_HIER_REF,                                      
        ir.ITEM_STATUS,                                                        
                   
        ir.CREATE_DATE,
        ir.COMMODITY_CODE,
        ir.NAT_UPC,
        ir.START_DATE,                                   
        COALESCE(ir.END_DATE, CAST('2999-12-31' AS DATE)) END_DATE,
        ir.LAST_UPDATE
 FROM   
       (
               SELECT distinct  x.STORE_NUMBER, 
                                x.UPC, 
                                x.DEPARTMENT
               FROM             EW.PRD_SCS x
               LEFT OUTER JOIN  EW.ITRM_R y            -- Invalid POS_DEPT_NUMBER in PRD_SALES_COST_SUMMARY.
                   ON  x.STORE_NUMBER =    y.STORE_NUMBER       -- But still valid item per STORE_NUMBER / UPC in IR.
                   AND x.UPC =             y.UPC
                   and x.DEPARTMENT =      y.POS_DEPT_NUMBER 
                   and y.PDN_END_DATE is null
               WHERE y.ITEM_REGION_ID IS NULL
       ) A        
join    EDW.STORE B  
            on  a.STORE_NUMBER = b.STORE_NUMBER
            and b.END_DATE is null  
join    EW.ITRM_R ir                                   -- Item is valid for STORE.
            on  b.REGION =          ir.REGION
            and a.STORE_NUMBER =    ir.STORE_NUMBER
            and a.UPC =             ir.UPC
            and ir.END_DATE is null
LEFT OUTER JOIN                  
        EDW.ORG_TEAM_REF  otr                                -- With possibly still valid POS_DEPT_NUMBER per OTR.
            on  b.REGION =          otr.REGION
            and a.STORE_NUMBER  =   otr.STORE_NUMBER
            and a.DEPARTMENT =      otr.POS_DEPT_NUMBER
            and otr.END_DATE is null  --2678712 records
-----------
where not exists
(
select store_number,upc,trim(leading '0' from POS_DEPT) as POS_DEPT,pos_dept_number from IE_VT1 vt
where a.STORE_NUMBER =  vt.store_number
and a.upc = vt.upc
and trim(leading '0' from coalesce(otr.POS_DEPT,'00000')) =  vt.POS_DEPT
and a.department =vt.pos_dept_number);  --2678712 22 Secs  

上述调查查询仅采用25K CPU SEC，但是即使在不存在的位置之后，最终计数也与“ 2678712记录”条款不存在之前的数字相同。

有人可以在此查询中如何实施如何实施吗？