sql优化，生产真实案例7

Sql文本：

SELECT  COUNT(1)
FROM    TBLMITEMCCLASS2MITEM M,
        (
        WITH TMP AS
                (
                        SELECT  F.VALUE
                        FROM    TBLPROFILEVALUE F
                        WHERE   F.PROFILECODE = 'MAIN_AND_SUB_PCB_RULE'
                )
        SELECT  REGEXP_SUBSTR(TMP.VALUE, '[^|]+', 1, ROWNUM) VALUE
        FROM    TMP CONNECT BY ROWNUM <= REGEXP_COUNT(TMP.VALUE, '\|') + 1
        )
        TMP,
        TBLBARPRINT T
WHERE   T.BARCODE   = :B2
        AND T.ORGID = :B1
        AND M.ORGID = T.ORGID
        AND T.ORGID
        ||
        ';'
        ||
        M.CONCATENATEDCLASSCODE = TMP.VALUE

执行1500万次，执行计划如下，对TBLMITEMCCLASS2MITEM全表扫描，cost为181

造成全表扫描的原因是把两列进行拼接，导致无法用上索引

    AND T.ORGID
    ||
    ';'
    ||
    M.CONCATENATEDCLASSCODE = TMP.VALUE

而且根据package中的内容，这个sql也仅用于判断是否为0，不考虑具体数值

不明白其中connect by的目的

建议修改为如下，由于对于具体数值无要求，改为取rownum=1，再count，即可判断是否为0
并且根据业务反馈，原有sql其实还缺少关联条件

造成多对多的关联，其实count的值并不对，原本应该count为0，缺少此条件导致count部位0

修改为如下内容：

SELECT COUNT(*)
  FROM (SELECT 1
          FROM tblmitemcclass2mitem m,
               (                   WITH tmp AS (SELECT f.value
                                                  FROM tblprofilevalue f
                                                 WHERE f.profilecode =
                                                       'MAIN_AND_SUB_PCB_RULE')
                 SELECT regexp_substr(tmp.value,
                                      '[^|]+',
                                      1,
                                      rownum) VALUE
                   FROM tmp
                 CONNECT BY rownum <= regexp_count(tmp.value,
                                                   '\|') + 1) tmp, tblbarprint t
                  WHERE t.barcode = &b2
                    AND t.orgid = &b1
                    AND m.orgid = t.orgid
                    AND m.mitemcode = t.itemcode
                    AND t.orgid || ';' || m.concatenatedclasscode = tmp.value
                    AND rownum = 1
        )