그렇군요. 문제가 있네요.

살펴보고 다시 올리겠습니다.

-- Oracle SQL Tuning 까페 http://cafe.daum.net/oraclesqltuning

한가지를 처리 안해줬었네요.

기존 쿼리와 수정된 쿼리를 올립니다. 빨간색 부분을 바꿔주시고 테스트 해보세요.

-- 기존 쿼리

SELECT *
  FROM a
 WHERE a.grp IN (
          SELECT   b.grp
              FROM b
             WHERE b.cd IN (SELECT c.cd
                              FROM c
                             WHERE c.tmp = :tmp_value)
          GROUP BY b.grp
            HAVING COUNT (*) >= (SELECT COUNT (*)
                                   FROM b bb
                                  WHERE bb.grp = b.grp) AND COUNT (*) > 1
          UNION
          SELECT   b.grp
              FROM b
             WHERE b.cd IN (
                      SELECT c.cd
                        FROM c
                       WHERE c.tmp = :tmp_value
                         AND c.cd NOT IN (
                                SELECT DISTINCT b.cd
                                           FROM b
                                          WHERE b.grp IN (
                                                   SELECT   b.grp
                                                       FROM b
                                                      WHERE b.cd IN (
                                                               SELECT c.cd
                                                                 FROM c
                                                                WHERE c.tmp =
                                                                         :tmp_value)
                                                   GROUP BY b.grp
                                                     HAVING COUNT (*) >=
                                                               (SELECT COUNT
                                                                           (*)
                                                                  FROM b bb
                                                                 WHERE bb.grp =
                                                                          b.grp)
                                                        AND COUNT (*) > 1)))
          GROUP BY b.grp
            HAVING COUNT (*) = 1)

-- 수정된 쿼리

SELECT *
  FROM a
 WHERE a.grp IN (
          SELECT   b.grp
              FROM b
             WHERE b.cd IN (SELECT c.cd
                              FROM c
                             WHERE c.tmp = :tmp_value)
          GROUP BY b.grp
            HAVING COUNT (*) >= (SELECT COUNT (*)
                                   FROM b bb
                                  WHERE bb.grp = b.grp) AND COUNT (*) > 1
          UNION
          SELECT   b.grp
              FROM b
             WHERE b.cd IN (
                      SELECT c.cd
                        FROM c
                       WHERE c.tmp = :tmp_value
                         AND c.cd NOT IN (
                                SELECT DISTINCT b.cd
                                           FROM b
                                          WHERE b.grp IN (
                                                   SELECT   b.grp
                                                       FROM b
                                                      WHERE b.cd IN (
                                                               SELECT c.cd
                                                                 FROM c
                                                                WHERE c.tmp =
                                                                         :tmp_value)
                                                   GROUP BY b.grp
                                                     HAVING COUNT (*) >=
                                                               (SELECT COUNT
                                                                           (*)
                                                                  FROM b bb
                                                                 WHERE bb.grp =
                                                                          b.grp)
                                                        AND COUNT (*) > 1)))
          GROUP BY b.grp
            HAVING (SELECT COUNT (*)
                      FROM b bb
                     WHERE bb.grp = b.grp) = 1) 

-- Oracle SQL Tuning 까페 http://cafe.daum.net/oraclesqltuning