查看: 3006|回复: 2

[原创] oracle 使用semi-join优化一例

[复制链接]
招聘 : Java研发
论坛徽章:
1
2010新春纪念徽章
日期:2010-03-01 11:19:06
发表于 2011-6-20 22:39 | 显示全部楼层 |阅读模式
blog文章链接地址:

http://www.dbafree.net/?p=224



贴一下文章,如下:



有一个查询,大致情况如下:
<pre>
SELECT COUNT(distinct A.BATCH_ID)
  FROM A, B
WHERE A.机构 = '类别'
   AND B.BATCH_ID = A.BATCH_ID;
</pre>   
A为批次表,4000条记录,B表为批次明细表,A表每个批次在B中对应5000条记录,所以B表大约在2000万条记录左右。A.机构 = '类别'的批次号大概600个。
上面查询的意思是:统计一下有批次明细的批次号,总共有多少个。
这个查询,我跑了大约10秒没没出来结果。原因很明显,它要先选出A表中XX类别的数据,过滤得到600个批次号,然后用这600个批次号跟B表所有的满足条件的记录做关联(大约600*5000=300万),取得结果后,再统计count( distinct batch_id)。
这个SQL转化一下其实就是下面这个SQL:
<pre>
SELECT COUNT(distinct A.BATCH_ID) FROM (<font color=red>
SELECT A.BATCH_ID
   FROM A, B
  WHERE A.机构 = '类别'
    AND B.BATCH_ID = A.BATCH_ID</font>
    );
</pre>  
这里先来说明一下子查询的执行代码(如上红色部分),大致如下:
<pre>
for x in ( select BATCH_ID from A where  A.机构 = '类别' )
   loop
       for y in ( select BATCH_ID from B)
           loop
               if ( x.BATCH_ID == y.BATCH_ID )
                   outPut_Record(x.BATCH_ID)
               End if
           end loop
   end loop
</pre>
这个代码如何优化呢?
代码的主要部分是:
<pre>
loop
    if ( x.BATCH_ID == y.BATCH_ID )
       OutPut_Record(x.BATCH_ID)
      End if
end loop
</pre>
对于这个查询而言,这个LOOP 循环,我们只要取到至少1条记录就足以判断结果了,所以,优化很容易,只要加个break就可以了。
<pre>
for x in ( select BATCH_ID from A where  A.机构 = '类别' )
   loop
       for y in ( select BATCH_ID from B)
           loop
               if ( x.BATCH_ID == y.BATCH_ID )
                   outPut_Record(x.BATCH_ID)
                   <font color=red>break;</font>
               End if
           end loop
   end loop
</pre>
--优化后的SQL即为,这个SQL只要判定B表中有没有BATCH_ID就可以了,即只要有一条,就终止执行。
这个优化后的SQL的逻辑读在3000个左右。执行效率已经比较高了。逻辑读个数约为:600*3+600*3=3600逻辑读。SQL语句如下:
<pre>
SELECT COUNT(a.BATCH_ID)
   FROM A
  WHERE A.机构 = '类别'
    and exists
  (select /*+ nl_sj(A B)*/
          1
           from B
          where b.BATCH_ID = a.BATCH_ID)
</pre>
其中<font color=red>nl_sj(A B)这个hints一般情况下可以不加</font>。

关于semi-join和Anti-Join,大致说明如下:

semi-join(半连接)
Suppose you have the DEPT and EMP tables in the SCOTT schema and you want a list of departments with at least one employee. You could write the query with a conventional join:

<pre>        SELECT   D.deptno, D.dname
        FROM     dept D, emp E
        WHERE    E.deptno = D.deptno
        ORDER BY D.deptno;
</pre>
Unfortunately, if a department has 400 employees then that department will appear in the query output 400 times. You could eliminate the duplicate rows by using the DISTINCT keyword, but you would be making Oracle do more work than necessary. Really what you want to do is specify a semi-join between the DEPT and EMP tables instead of a conventional join:
<pre>
        SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno;
</pre>

多在子查询exists中使用,对外部row source的每个键值,查找到内部row source匹配的第一个键值后就返回,如果找到就不用再查找内部row source其他的键值了。
<pre>
for x in ( select * from dept )
   loop
       for y in ( select * from emp)
           loop
               if ( x.deptno == y.deptno )
                   OutPut_Record(x.dname)
                   Break;
               End if
           end loop
   end loop
</pre>

另外,还有一个Anti-Join(反连接)
Suppose you want a list of empty departments—departments with no employees. You could write a query that finds all departments and subtracts off the department of each employee:
<pre>
        SELECT   D1.deptno, D1.dname
        FROM     dept D1
        MINUS
        SELECT   D2.deptno, D2.dname
        FROM     dept D2, emp E2
        WHERE    D2.deptno = E2.deptno
        ORDER BY 1;
</pre>
The above query will give the desired results, but it might be clearer to write the query using an anti-join:
<pre>
        SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    NOT EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno;
</pre>
论坛徽章:
0
发表于 2011-6-22 10:00 | 显示全部楼层
友情up

使用道具 举报

回复
招聘 : SAP实施
论坛徽章:
9
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442010新春纪念徽章
日期:2010-03-01 11:20:53SQL大赛参与纪念
日期:2011-04-13 12:08:17ITPUB十周年纪念徽章
日期:2011-11-01 16:20:282012新春纪念徽章
日期:2012-01-04 11:50:44茶鸡蛋
日期:2012-02-29 11:04:04鲜花蛋
日期:2012-03-13 13:54:13蛋疼蛋
日期:2012-05-28 16:48:46
发表于 2011-6-22 13:05 | 显示全部楼层
写的不错。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表