【讨论】SQL优化请教（connect by start with相关）

tangyuxint · 发表于 2014-12-26 14:13

SQL优化，connect by start with 相关，start with 后面跟子句查询会很慢走全表扫描，但是跟常量字符串又很快
1.这个运行正常
SELECT LEVEL
      , LPAD(PARTS_CODE, LENGTH (PARTS_CODE) + (LEVEL * 2 ) - 2 , ' ') PARTS_CODE
      , DESCRIPTION
      ,COMPONTNT_QUANTITY
      ,GP_FLAG
      ,OPERATION_SEQ_NUM
      ,MFG_CODE
      ,PARTS_CODE
      , PRIOR MFG_CODE
      ,CONNECT_BY_ROOT MFG_CODE AS ROOT_NAME
FROM (SELECT MSI1.SEGMENT1       PARTS_CODE
                  ,MSI1.DESCRIPTION
                  ,MSI2.SEGMENT1       MFG_CODE
                  ,IC.COMPONENT_QUANTITY COMPONTNT_QUANTITY
                  ,IC.OPERATION_SEQ_NUM
                  ,IC.ATTRIBUTE11       GP_FLAG
         FROM BOM_BILL_OF_MATERIALS BOM
                  ,BOM_INVENTORY_COMPONENTS IC
                  ,MTL_SYSTEM_ITEMS       MSI1
                  ,MTL_SYSTEM_ITEMS       MSI2
         WHERE  MSI1.INVENTORY_ITEM_ID = IC.COMPONENT_ITEM_ID
         AND MSI1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
         AND IC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
         AND BOM.ORGANIZATION_ID = 105
         AND BOM.ALTERNATE_BOM_DESIGNATOR = 'MPL'
         AND BOM.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
         AND BOM.ASSEMBLY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
         AND IC.EFFECTIVITY_DATE <= SYSDATE
         AND (IC.DISABLE_DATE IS NULL OR IC.DISABLE_DATE >= SYSDATE ))
start with MFG_CODE in ( 'ABC' ) --主要问题在这里
CONNECT BY PRIOR PARTS_CODE = MFG_CODE

执行计划截图如下：

2.如果这个限制条件改为一个子查询，效率就会变得十分低下
实际用的时候我从另外一个表直接select MFG_CODE，但是跑了很久就是跑不出
SELECT LEVEL
      , LPAD(PARTS_CODE, LENGTH (PARTS_CODE) + (LEVEL * 2 ) - 2 , ' ') PARTS_CODE
      , DESCRIPTION
      ,COMPONTNT_QUANTITY
      ,GP_FLAG
      ,OPERATION_SEQ_NUM
      ,MFG_CODE
      ,PARTS_CODE
      , PRIOR MFG_CODE
      ,CONNECT_BY_ROOT MFG_CODE AS ROOT_NAME
FROM (SELECT MSI1.SEGMENT1       PARTS_CODE
                  ,MSI1.DESCRIPTION
                  ,MSI2.SEGMENT1       MFG_CODE
                  ,IC.COMPONENT_QUANTITY COMPONTNT_QUANTITY
                  ,IC.OPERATION_SEQ_NUM
                  ,IC.ATTRIBUTE11       GP_FLAG
         FROM BOM_BILL_OF_MATERIALS BOM
                  ,BOM_INVENTORY_COMPONENTS IC
                  ,MTL_SYSTEM_ITEMS       MSI1
                  ,MTL_SYSTEM_ITEMS       MSI2
         WHERE  MSI1.INVENTORY_ITEM_ID = IC.COMPONENT_ITEM_ID
         AND MSI1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
         AND IC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
         AND BOM.ORGANIZATION_ID = 105
         AND BOM.ALTERNATE_BOM_DESIGNATOR = 'MPL'
         AND BOM.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
         AND BOM.ASSEMBLY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
         AND IC.EFFECTIVITY_DATE <= SYSDATE
         AND (IC.DISABLE_DATE IS NULL OR IC.DISABLE_DATE >= SYSDATE ))
start with MFG_CODE in ( select 'ABC' from dual )  --改了个子查询就很慢，整个执行计划都变了
CONNECT BY PRIOR PARTS_CODE = MFG_CODE
执行计划截图如下：有hash jion 还有全表扫描

大家有空的帮忙看看，如何把第二SQL的执行计划跑的和第一个1一样啊,感激不尽啊~