<% dim ModuleName,InfoID,ChannelShortName,CorrelativeArticle,InstallDir,ChannelDir,Keyword,PageTitle,ArticleIntro,Articlecontent Keyword=stripHTML("嵌套,数据") PageTitle=stripHTML("Oracle表联结之嵌套循环") ArticleIntro=stripHTML("Oracle表联结之嵌套循环,1.单表访问:分表,分区,建索引,全表扫描---开并行,永远把它放内存,压缩2.多表关联,任何时刻只能是2个表关联,得到的结果集再和其他表关联。3.嵌套循环:Oracle") Articlecontent=stripHTML("1.单表访问: 分表,分区,建索引,全表扫描---开并行, 永远把它 放内存,压缩2.多表关联,任何时刻只能是2个表关联,得到的结果集再和其他表关联。3.嵌套循…") ModuleName = stripHTML("classical") InfoID = stripHTML("210850") ChannelShortName=stripHTML("文章") InstallDir=stripHTML("http://www.77169.com/") ChannelDir=stripHTML("classical") %> Oracle表联结之嵌套循环 - 华盟网 - http://www.77169.com  <% Function stripHTML(strHTML) 'Strips the HTML tags from strHTML Dim objRegExp, strOutput Set objRegExp = New Regexp objRegExp.IgnoreCase = True objRegExp.Global = True objRegExp.Pattern = "<.+?>" 'Replace all HTML tag matches with the empty string strOutput = objRegExp.Replace(strHTML, "") 'Replace all < and > with < and > strOutput = Replace(strOutput, "<", "<") strOutput = Replace(strOutput, ">", ">") strOutput = Replace(strOutput,Chr(34),Chr(39)) stripHTML = strOutput 'Return the value of strOutput Set objRegExp = Nothing End Function %> "> 
您现在的位置: 华盟网 >> 知识库 >> 数据库 >> Oracle >> 基础教程 >> 正文

Oracle表联结之嵌套循环

2015/9/8 作者:不祥 来源: 互联网
导读 <% if len(ArticleIntro)<3 then Response.Write Articlecontent 'Response.Write "Articlecontent" else Response.Write ArticleIntro 'Response.Write "ArticleIntro" end if %>

1.单表访问: 分表,分区,建索引,全表扫描---开并行, 永远把它 放内存,压缩

2.多表关联,任何时刻只能是2个表关联,得到的结果集再和其他表关联。

3.嵌套循环:Oracle从较小结果集(驱动表/外部表)中读取一行,然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接),如果符合规则,就放入结果集中,然后取较小结果集的下一条数据继续进行循环,直到结束。嵌套循环只适合输出少量结果集或者是用于快速输出结果集。其实相当于双层FOR循环。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID bv300dy9b7gyn, child number 0

-------------------------------------

select /*+ first_rows */ e.ename,e.job,d.dname from emp e,dept d where

e.deptno=d.deptno and e.sal<2000

Plan hash value: 3625962092

-----------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

-----------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.14 | 18 | 8 |

| 1 | NESTED LOOPS | | 1 | 4 | 7 |00:00:00.14 | 18 | 8 |

| 2 | NESTED LOOPS | | 1 | 4 | 7 |00:00:00.14 | 11 | 7 |

|* 3 | TABLE ACCESS FULL | EMP | 1 | 4 | 7 |00:00:00.12 | 7 | 6 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 7 | 1 | 7 |00:00:00.01 | 4 | 1 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 1 | 7 |00:00:00.01 | 7 | 1 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("E"."SAL"<2000)

4 - access("E"."DEPTNO"="D"."DEPTNO")

24 rows selected.

离关键字近的是驱动表,嵌套循环的rows是错误的,嵌套循环的算法,比如a NL b,如a表有1000条,从a表中取1000条数据,扫描一次a,把这1000条数据传给b,然后b表被扫描1000次,那么取出的a表的数据放在什么地方?匹配完一条然后立马返回,NL不需要PGA,因为不用缓存数据,如果多层NL,仍然是存PGA,多层NL容易引起CBC,

嵌套循环中,过滤后返回结果集的小的当驱动表,在外连接中,嵌套循环不能修改驱动表,在嵌套循环中,被驱动表的连接列一定要有索引,从上面执行计划可以看到E的deptno传值给D表的deptno,驱动表的连接列不用建索引,

nl 必须是驱动表返回数据量很少的时候才走,在sql语句中有count,group by,distinct,sum等关键字,不能走NL,如果OLTP系统,有大量的distinct,只能说明表设计有问题,用中间表把所有的关联去重解决distinct,

如果在执行计划里面有很多NL,从最里面开始搞,如果最里面错误了,那么外面的NL全部错误,由里向外不断看NL.

怎样判断NL是否是对的?1,看驱动表返回的数据量,2,看被驱动表是否走索引,3.看最终返回多少结果集。那么第3条最重要。最终返回多少结果集决定是否走NL还是HASH.

如果A NL B,返回10w条数据,如果a:b=1:1,那么a至少返回10w条数据,然后b表被扫描10w次,如果a:b=1:10,那么a至少返回1w数据,那么b表被扫描1w次,b表走索引,b表走一次索引,回表10条数据,那么b表总回表次数是10w次,所以在NL中,被驱动表不管被扫描多少次,那么回表次数是最终返回数据条数,所以嵌套循环不适合大量数据,根本原因在于回表或者回表再过滤,如果不用回表或回表再过滤,那么NL非常有效。

被驱动表的连接列要基数很高,如果基数很低,不能走NL,如1:1w,1:N,然后N太大

NL只需要SGA,不需要PGA,NL支持非等值jion,而HASH join只支持等值关联。

判断是否走NL和HASH,根据最终返回的结果集来判断,其次驱动表返回的行数,再是被驱动表的jion列的基数。

错误NL,1.单次返回大量数据,如100w

附:查询访问表所有字段的sql信息:

WITH t AS

(SELECT a.object_name "表名",

a.sql_id,

c.sql_text,

c.executions,

(regexp_count(prjection, ',', 1) + 1) / 2 "访问列数",

COUNT(b.column_name) "总列数",

d.bytes / 1024 / 1024 "表体积_MB"

FROM v$sql_plan a, dba_tab_columns b, v$sql c, dba_segments d

WHERE a.object_owner = b.owner

AND a.object_name = b.table_name

AND a.object_owner = 'EOL'

AND a.sql_id = c.sql_id

AND a.object_name = d.segment_name

AND a.object_owner = d.owner

GROUP BY (regexp_count(prjection, ',', 1) + 1) / 2,a.object_name,

a.sql_id,

c.sql_text,

c.executions,

d.bytes / 1024 / 1024)

SELECT * FROM t WHERE t.访问列数 = t.总列数 ORDER BY 表体积_MB,executions DESC;

                  微信群名称:华盟-黑白之道         华盟--黑白之道⑦QQ群: 9430885

  • 上一篇文章:

  • 下一篇文章: 没有了
  • 
    网友评论
      验证码
     
    

    关注

    分享

    0

    讨论

    2

    猜你喜欢

      <%=CorrelativeArticle%>

    论坛最新贴