Oracle数据库中级培训执行计划第7讲.ppt
- 文档编号:1383580
- 上传时间:2022-10-21
- 格式:PPT
- 页数:68
- 大小:789.50KB
Oracle数据库中级培训执行计划第7讲.ppt
《Oracle数据库中级培训执行计划第7讲.ppt》由会员分享,可在线阅读,更多相关《Oracle数据库中级培训执行计划第7讲.ppt(68页珍藏版)》请在冰豆网上搜索。
Oracle数据库中级培训(第7讲),上海全成通信技术有限公司金刚2010-06,培训大纲,一.连接选择率及基数二.连接机制嵌套循环散列(哈希)连接归并连接三.优化提示四.理解并行执行计划,连接基数,关于连接需要了解的几个知识点:
1)Oracle一次只能连接两个表。
不管查询中有多少个表,Oracle在连接中一次仅能操作两个对象。
2)到目前为止,优化器没有长期策略,它只是在当前已经得到的结果上连接下一个可用的表,以确定接下来的情况。
3)当执行5个表的连接时,优化器从一个表开始,将它与另一个表连接;然后将中间结果与下一个表连接,以此类推,直到处理完5个表为止。
连接基数和选择率,连接选择率:
(t1.c1=t2.c2)Sel(Pj)=1/max(NDV(t1.c1),NDV(t2.c2)(card(t1)-#t1.c1NULLS)/card(t1)(card(t2)-#t2.c2NULLS)/card(t2)连接基数:
Card(Pj)=card(t1)card(t2)Sel(Pj)补充:
GROUPBY分组基数Card(groupbyc1,c2,cn)=NDV(c1)NDV(c2)NDV(cn)2(n-1),连接基数计算示例,连接选择率和基数计算示例:
selecta.city_name,a.city_id,b.use_countfromdms_areaajoinfct_use_daybona.city_id=b.dms_area_idwherea.city_name=上海andb.dms_time_id=20101001;-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|37741|884K|5291(7)|00:
01:
36|*1|HASHJOIN|37741|884K|5291(7)|00:
01:
36|*2|TABLEACCESSFULL|DMS_AREA|1|10|3(0)|00:
00:
01|3|PARTITIONRANGESINGLE|13M|174M|5220(5)|00:
01:
34|574|574|*4|TABLEACCESSFULL|FCT_USE_DAY|13M|174M|5220(5)|00:
01:
34|574|574|-分析如下:
CARD(a)=1CARD(b)=13M(13058501)#CARDanulls=0#CARDbnulls=0NDV(a.city_id)=1NDV(b.city_id)=346Sel=1/(max(NDV(a.city_id),NDV(b.dms_area_id)*(carda-#cardanulls)/carda*(cardb-#cardbnulls)/cardb=1/(max(1,346)*1*1=1/346CARD(Pj)=card(a)*card(b)*Sel(Pj)=1*13058501*1/346=37741,GroupBY基数计算示例,Groupby连接基数计算explainplanforselectdms_area_id,DMS_SERVICE_TYPE_ID,DMS_ENTRANCE_ID,count(*)fromfct_use_daybwhereb.dms_time_id=20101001groupbydms_area_id,DMS_SERVICE_TYPE_ID,DMS_ENTRANCE_ID;select*fromtable(dbms_xplan.display);-|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|3253K|58M|26867(4)|00:
08:
04|1|HASHGROUPBY|3253K|58M|498M|26867(4)|00:
08:
04|2|PARTITIONRANGESINGLE|13M|236M|5166(4)|00:
01:
33|574|574|*3|TABLEACCESSFULL|FCT_USE_DAY|13M|236M|5166(4)|00:
01:
33|574|574|-NDV(dms_area_id)=346NDV(DMS_SERVICE_TYPE_ID)=6268NDV(DMS_ENTRANCE_ID)=3CARD(groupby)=346*6268*3/2(3-1)=3253092,培训大纲,一.连接选择率及基数二.连接机制嵌套循环散列(哈希)连接归并连接三.优化提示四.理解并行执行计划,嵌套循环连接(原理),嵌套循环实现机制(伪代码):
Forr1in(selectrowsfromtable_1wherecolx=value)loopforr2in(selectrowsfromtable_2thatmatchcurrentrowfromtable_1)loopoutputvaluesfromcurrentrowoftable_1andcurrentrowoftable_2;endloop;Endloop;伪代码解释:
这段代码由两个循环构成。
嵌套循环中的这两个表通常称为外部表(outertable)和内部表(innertable)。
在嵌套循环连接中,外部表又称为驱动表(drivertable),嵌套循环连接(要点),嵌套循环连接的相关知识点:
1)嵌套循环连接可以用两幅典型的图来表示。
作为解释嵌套循环连接机制的工具,每副图有各自的优点和不足。
2)嵌套循环通常用于从驱动表中过滤得到少数行中的每一行用于连接第二个表以便从从选取“少数行”。
嵌套循环连接(机制一),该图简单的将一个表中的行与另一个表中的行连接起来,用箭头表示活动的方向,这种表示使我们很容易看出一个表中的行与另一个表中对应行之间的联系。
很明显,内表在连接列上不存在对应的索引或存在但没有使用。
我们要尽可能规避这种连接实现机制。
嵌套循环连接(机制二),该图表示利用了第二个表的索引,因为当存在嵌套循环连接时,通常都以这种方式涉及到索引。
该图的优点是可以直观显示出嵌套循环的新旧两种机制。
嵌套循环连接(机制二:
旧机制),在旧机制中,首先在外部(驱动)表中找到第一行,然后遍历索引,依次访问内部表中每个匹配的行;接下来对外部表中的第二行和第三行重复上述过程,其结果是按照(a,a,a,b,b,b,c,c,c)的顺序取出第二个表中的行,可以使后面的orderby子句完成SORT(ORDERBY)NOSORT操作。
嵌套循环连接(机制二:
新机制),在新机制中,首先在外部表中找到第一行,然后遍历索引,但停止在叶块上,只为内部表选取相关的rowid;接下来对外部表中的第二行和第三行重复上述过程。
当找到全部目标rowid之后对其进行排序,然后只对内部表按顺序访问一遍,按照其rowid的顺序选取出相应的行,该例中的顺序是(a,b,b,a,c,b,c,a,c).,嵌套循环连接(COST),嵌套循环连接成本主要涉及到下列要素:
1)从第一个表中取出全部所需的行的成本是多少。
2)第一个表中返回多少行3)根据从第一个表中当前行获得的新的可用信息,在第二个表中查找一次相关行通常的成本是多少结论:
NESTLOOPJOINCOST=从第一个表取得数据的成本+从第一个表得到结果的基数对第二个表访问一次的成本,嵌套循环连接示例,嵌套循环连接成本计算示例:
selecta.city_name,a.city_id,b.use_countfromdms_areaajoinfct_use_daybona.city_id=b.dms_area_idwherea.city_name=上海andb.dms_time_id=20101001;-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|-|0|SELECTSTATEMENT|1|49|3(0)|00:
00:
01|1|NESTEDLOOPS|1|49|3(0)|00:
00:
01|2|PARTITIONRANGESINGLE|1|39|2(0)|00:
00:
01|574|574|*3|TABLEACCESSFULL|FCT_USE_DAY|1|39|2(0)|00:
00:
01|574|574|*4|TABLEACCESSBYINDEXROWID|DMS_AREA|1|10|1(0)|00:
00:
01|*5|INDEXUNIQUESCAN|DMS_AREA_PK|1|0(0)|00:
00:
01|-,哈希连接,哈希连接又叫散列连接。
散列连接可以在3种不同层次上执行,分别为:
1)Optimal(最优散列连接)2)Onepass(一遍散列连接)3)Multipass(多遍散列连接),最优散列连接
(1),执行步骤如下:
1)Oracle读取第一个数据集并在内存中建立一个“散列桶(hashbucket)”的数组。
散列表中桶的数目一般是2的偶数次幂(对于小的散列连接,桶的数目通常为1024或4096)。
虽然整个结构是一个固定数组和链表的复杂集合,但我们可以简单的将散列表想象为一个方阵。
第一个表(构建表)中的行随机分布在方阵之中。
最优散列连接
(2),执行步骤如下:
2)Oracle开始读取第二个表(探查表,probetable),采用最适合获取行的访问机制,并且在连接列上使用同样的散列函数计算相关散列桶的号码。
然后Oracle查看该桶中是否有行-这称为探查散列表(Probingthehashtable).,最优散列连接(3),执行步骤如下:
3)如果相应的桶中没有行,则Oracle会立即丢弃从探查表中取出的这一行。
如果相应的桶中有一些行,则Oracle会精确地检查连接列以判断是否存在合适的匹配。
由于连接列上具有不同值的行有可能在同一个散列桶中,因此必须执行精确检查。
对于通过精确检验的行,就可以立即报告了。
Oracle为散列分配内存的方式与为排序分配内存的方式有着重要的区别。
一旦散列连接开始Oracle就会要求hash_area_size内存量的一大部分,以便创建大量的散列桶,这有助于将散列冲突减到最少。
最优散列连接(4),更深一层次理解:
最优散列连接实际上只是对动态创建的单表散列簇的从后到前的嵌套循环。
我们利用从构建表中选取出的行,在本地内存中建立一个单表散列簇,然后对于从探查表中选取出的每一行,利用散列键检查该散列簇。
最忧散列连接的主要好处是构建表被转移到本地内存中,而不是作为缓冲存储器中真正的单表散列簇。
这意味着通常存在于表访问中的锁存、缓冲和读一致性成本,在探查散列时候不会出现。
最优散列连接的近似估计成本=从构建中获取数据的成本+从探查表中获取数据的成本,一遍散列连接
(1),执行步骤如下:
1)Oracle读取第一个数据集并将其分布到散列表中。
一旦使用某个桶,则设置该桶在位图中的对应位。
一遍散列连接
(2),执行步骤如下:
2)随着内存的使用,当可用内存用完时,簇被转储到磁盘上,转储时采用一种谨慎的策略,试图将尽可能多的完整分区在内存中保留尽可能长的时间。
当构建表处理完毕时,有可能一些分区仍完整的保留在内存中,而其它分区只有一些(但至少要有一个)簇保留在内存中。
位图总是完整的保留在内存中。
此时,Oracle将散列表整理完毕,将尽可能多的完整分区装入内存,但其余分区中超出的部分转储到磁盘。
作为重建的一部分,Oracle会保留一些簇(每个分区至少一个)用于处理探查表。
一遍散列连接(3),执行步骤如下:
3)一旦散列表整理完毕,Oracle就开始获取第二个数据集中的行,在每个行的连接列
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 中级 培训 执行 计划