ORACLE技术文档oracle 驱动表包含 hint使用 nested loop hash join sortmergegate方式.docx
《ORACLE技术文档oracle 驱动表包含 hint使用 nested loop hash join sortmergegate方式.docx》由会员分享,可在线阅读,更多相关《ORACLE技术文档oracle 驱动表包含 hint使用 nested loop hash join sortmergegate方式.docx(16页珍藏版)》请在冰点文库上搜索。
![ORACLE技术文档oracle 驱动表包含 hint使用 nested loop hash join sortmergegate方式.docx](https://file1.bingdoc.com/fileroot1/2023-5/2/c825a214-720e-4266-994a-452392686bf8/c825a214-720e-4266-994a-452392686bf81.gif)
ORACLE技术文档oracle驱动表包含hint使用nestedloophashjoinsortmergegate方式
Oracle驱动表
Oracle驱动表(drivingtable/outertable)也叫做外部表,也叫外层表,是在多表关联查询中首先遍历的表,驱动表的每一行都要到另一个表中寻找相应的记录,然后计算返回最终数据。
驱动表的概念只在nestedloops和hashjoin时存在。
原则:
1.驱动表一般是小表,但不绝对,看下边
2.驱动表一般是通过where条件筛选后剩余行数较少的表。
3.如果表的一条记录很长,占用几个数据块也适合做驱动表
4.CBO和RBO中,对于驱动表的选择是不同的,CBO中通过对统计信息的参考进行计算来选择驱动表,而RBO中按照既定原则选择驱动表。
5.RBO中,from后边最右边的表为驱动表(from后边表从右向左遍历,where条件从下向上遍历)
6.涉及驱动表的查询,连接条件的索引很重要,驱动表连接字段可以没有索引,但是被驱动表需要被扫描驱动表经过筛选后剩余条数的遍数,所以被驱动表的连接字段上有一条索引是非常重要的。
分析:
假设a表10行记录,b表1000行记录,两个表都有id列,查询时使用id列进行关联
Select*froma,bwherea.id=b.idanda.id=100;
A表作为驱动表比较合适,假设a.id=100只有1行,即使全表扫描a表也就几个块,假设a表占用10个块。
B表的id假如非唯一,如果b表的id列有索引,b表占用100个块,每个块10行记录,id列索引占用10个块,并且id为100有2条记录,在两个块中
那么这条语句的成本(以块计算,下同):
A表(10个块)*b表索引(10个块)+b表id为100的2个块=102个块
如果b表没有索引,成本为:
A表(10个块)*b表(100个块)=1000个块
如果a,b表都没有索引,可以看出不管哪个表作为驱动表,语句的执行成本都是一样的。
如果a,b表id列都有索引,a表id列索引占2个块,成本为:
A表id列索引(2个块)*b表id列索引(10个块)+b表id为100的2个块=22个块
如果B表的记录很长,可以作为驱动表的情况比较复杂,大家可以自己想象适合的场景。
可以看出,在连接中,如果连接列有索引是多么的重要。
实验支撑
SQL>createtablea(id,name)asselectobject_id,object_namefromall_objectswhererownum<200;
Tablecreated.
SQL>
SQL>createtablebasselect*fromall_objects;
Tablecreated.
SQL>selectcount(*)froma;
COUNT(*)
----------
199
SQL>selectcount(*)fromb
SQL>
COUNT(*)
----------
89083
SQL>
SQL>execdbms_stats.gather_table_stats('TEST','A');
PL/SQLproceduresuccessfullycompleted.
SQL>
SQL>execdbms_stats.gather_table_stats('TEST','B');
PL/SQLproceduresuccessfullycompleted.
两个表都没有索引
Selectcount(*)froma,bwherea.id=b.object_id
Anda.id=53
执行计划:
(B表驱动)
SQL>Selectcount(*)froma,bwherea.id=b.object_id
2Anda.id=53
3/
COUNT(*)
----------
1
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
319234518
----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|9|420
(1)|00:
00:
01|
|1|SORTAGGREGATE||1|9|||
|*2|HASHJOIN||1|9|420
(1)|00:
00:
01|
|*3|TABLEACCESSFULL|B|1|5|417
(1)|00:
00:
01|
|*4|TABLEACCESSFULL|A|1|4|3(0)|00:
00:
01|
----------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("A"."ID"="B"."OBJECT_ID")
3-filter("B"."OBJECT_ID"=53)
4-filter("A"."ID"=53)
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
1506consistentgets
0physicalreads
0redosize
542bytessentviaSQL*Nettoclient
543bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>
A表作为驱动表
SQL>Select/*+ordereduse_nl(a)*/count(*)froma,bwherea.id=b.object_id
2Anda.id=53;
COUNT(*)
----------
1
1rowselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
1397777030
----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|9|420
(1)|00:
00:
01|
|1|SORTAGGREGATE||1|9|||
|*2|HASHJOIN||1|9|420
(1)|00:
00:
01|
|*3|TABLEACCESSFULL|A|1|4|3(0)|00:
00:
01|
|*4|TABLEACCESSFULL|B|1|5|417
(1)|00:
00:
01|
----------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("A"."ID"="B"."OBJECT_ID")
3-filter("A"."ID"=53)
4-filter("B"."OBJECT_ID"=53)
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
1506consistentgets
0physicalreads
0redosize
542bytessentviaSQL*Nettoclient
543bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>
发现上面两个语句的代价是一样的
/*+Ordereduse_nl(table_name)*/--使用hint强制表作为驱动表,只使用/*+use_nl(table1,table2)是无法强制驱动表顺序的,另外,这里使用的use_nl,但是走的是hashjoin,说明在没有索引的情况下,oracle优化器更倾向hashjoin,因为nestedloop并不一定会提前返回数据。
执行计划下,hashloop下第一个表为驱动表。
表Bobject_id列有索引的情况
SQL>createindexid_b_object_idonb(object_id);
Indexcreated.
SQL>execdbms_stats.gather_table_stats(ownname=>'TEST',TABNAME=>'B',CASCADE=>TRUE);
PL/SQLproceduresuccessfullycompleted.
SQL>
执行计划:
SQL>Selectcount(*)froma,bwherea.id=b.object_id
2Anda.id=53;
COUNT(*)
----------
1
1rowselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
3168189658
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|9|4(0)|00:
00:
01|
|1|SORTAGGREGATE||1|9|||
|2|MERGEJOINCARTESIAN||1|9|4(0)|00:
00:
01|
|*3|TABLEACCESSFULL|A|1|4|3(0)|00:
00:
01|
|4|BUFFERSORT||1|5|1(0)|00:
00:
01|
|*5|INDEXRANGESCAN|ID_B_OBJECT_ID|1|5|1(0)|00:
00:
01|
----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
3-filter("A"."ID"=53)
5-access("B"."OBJECT_ID"=53)
Statistics
----------------------------------------------------------
92recursivecalls
0dbblockgets
134consistentgets
23physicalreads
0redosize
542bytessentviaSQL*Nettoclient
543bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
12sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>
发现执行计划并没有使用nestedloop和hashjoin,不过走索引后,执行代价明显减少。
Mergejoin发生了排序,如果内存够用还好,不够用就比较耗时了。
强制hash
A表驱动
SQL>Select/*+use_hash(a,b)*/count(*)froma,bwherea.id=b.object_id
2Anda.id=53;
COUNT(*)
----------
1
1rowselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
895278611
--------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|9|4(0)|00:
00:
01|
|1|SORTAGGREGATE||1|9|||
|*2|HASHJOIN||1|9|4(0)|00:
00:
01|
|*3|TABLEACCESSFULL|A|1|4|3(0)|00:
00:
01|
|*4|INDEXRANGESCAN|ID_B_OBJECT_ID|1|5|1(0)|00:
00:
01|
--------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("A"."ID"="B"."OBJECT_ID")
3-filter("A"."ID"=53)
4-access("B"."OBJECT_ID"=53)
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
5consistentgets
0physicalreads
0redosize
542bytessentviaSQL*Nettoclient
543bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>
--强制使用hashjoin,a表默认变为了驱动表,执行代价很低,符合要求
B表驱动
SQL>Select/*+ordereduse_hash(b)*/count(*)froma,bwherea.id=b.object_id
2Anda.id=53;
COUNT(*)
----------
1
1rowselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
895278611
--------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|9|4(0)|00:
00:
01|
|1|SORTAGGREGATE||1|9|||
|*2|HASHJOIN||1|9|4(0)|00:
00:
01|
|*3|TABLEACCESSFULL|A|1|4|3(0)|00:
00:
01|
|*4|INDEXRANGESCAN|ID_B_OBJECT_ID|1|5|1(0)|00:
00:
01|
--------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("A"."ID"="B"."OBJECT_ID")
3-filter("A"."ID"=53)
4-access("B"."OBJECT_ID"=53)
Statistics
----------------------------------------------------------
1recursivecalls
0dbblockgets
5consistentgets
0physicalreads
0redosize
542bytessentviaSQL*Nettoclient
543bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>
发现有索引,并且有统计信息的情况下,无法强制B表作为驱动表,oracle对hint进行了忽略。
删除统计信息试试:
SQL>EXECdbms_stats.delete_table_stats(user,'B',cascade_parts=>TRUE);
PL/SQLproceduresuccessfullycompleted
SQL>EXECdbms_stats.delete_table_stats(user,'A',cascade_parts=>TRUE);
PL/SQLproceduresuccessfullycompleted
SQL>
--测试发现仍然不能将B表作为驱动表,修改optimizer_mode为rule
altersessionsetoptimizer_mode=rule;
SQL>Select/*+ordereduse_nl(b)*/count(*)froma,bwherea.id=b.object_id
2Andobject_id=53;
--发现仍然不能将B表作为驱动表
强制nestedloop
SQL>Select/*+ordereduse_nl(b)*/count(*)froma,bwherea.id=b.object_id
2Andobject_id=53;
COUNT(*)
----------
1
1rowselected.
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
1183094437
--------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost