SELECT a.mm , SUM(a.amount) amount FROM ( SELECT mm , amount FROM tb1@DBLINK WHERE mm = 1 ) A, tb2 B WHERE a.mm = b.mm GROUP BY a.mm
위쿼리를 실행하면 건수는 0 인데 시간은 상당히 오래걸립니다.
분홍색으로 보이는 쿼리, tb2 테이블을 개별적으로 돌리면 빠른데
조인을 걸면 너무 느리네요.
어떻게 처리를 해야 하나요??
trim(a.mm) = b.mm
이나 outer join 을 해보시길..
SELECT /*+ use_nl(a b) */ a.mm , SUM(a.amount) amount FROM ( SELECT /*+ driving_site(x) */ mm , amount FROM tb1@DBLINK x WHERE mm = 1 ) A, tb2 B WHERE a.mm = b.mm GROUP BY a.mm
또는
SELECT /*+ driving_site(a) use_nl(a b) */ a.mm , SUM(a.amount) amount FROM ( SELECT mm , amount FROM tb1@DBLINK x WHERE mm = 1 ) A, tb2 B WHERE a.mm = b.mm GROUP BY a.mm
SELECT /*+ ordered use_nl(a b) */ a.mm , SUM(a.amount) amount FROM ( SELECT mm , amount FROM tb1@DBLINK x WHERE mm = 1 ) A, tb2 B WHERE a.mm = b.mm GROUP BY a.mm
돌려보고 빠른걸로 쓰세요..