ORACLE_高效SQL分析.ppt

用提示控制NL的驱动表 1./*+use_nl(t2,t) */ 仅仅提示oracle走nest loop，没有指定任何驱动表 2./*+ ordered use_nl(t2,t) */走nest loop，order指定以from后面的第一个表做为驱动表。 3./*+ leading(t2) use_nl(t) */ 走nest loop，提示t2为驱动表。 结论：use_nl不能让优化器确定谁是驱动表谁是被驱动的表,use_nl(t,t2)也没有指出哪个是驱动表，这时候我们需要使用ordered,leading来强制指定驱动表，以达到我们的目的。 ordered这个HINT是表示表的连接顺序按from后面表的顺序依次连接的，有多张表关联的时候也是走这个顺序，在多表关联的时候稳定执行计划这个HINT用得比较多，要注意from后面表的连接顺序不能随意更改。 leading这个HINT主要是用来明确指定连接中的驱动表。 swap_join_inputs：比如有A、B、C三张表需要关联，a.col=b.col and a.col1=c.col1，这时候理想的执行计划是A和B先HASH JOIN，得出来的结果集再和C关联，由于相对来说C和A、B得出的结果集相比还是小表，显然用C来做为二次HASH JOIN的驱动表比较合适，那么就可以用swap_join_inputs来控制了。 用提示控制NL的驱动表示例 * 哈希连接(Hash Join, HJ) 哈希链接：在哈希连接中,Oracle访问一张表(驱动表，通常是较小的表),并在特定内存区(hash_area_size )中建立一张基于连接键的哈希表。然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否有匹配的记录。 哈希连接有效的条件： 设置hash_join_enabled = true，缺省为 TRUE 设置hash_area_size参数为适当的值，哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。 特点： a) 只能用于等值连接中; b) 这种连接方法，理论先进，其效率应该最高，但是它只能用在CBO优化器中，且需要设置合适的hash_area_size参数，才能取得较好的性能。 c) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。 哈希连接(Hash Join, HJ)的执行计划展示1 哈希连接(Hash Join, HJ)的执行计划展示2 三种表连接的对比 a)只能用户CBO优化器中 的于等值连接中！ b)一般是最高效率，合理 设置hash_area_size， 能得较好性能。 c)当2个较大的row source 连接时会取得相对较好的 效率，在一个row source 较小时则效率更好。 a)外部表比较小，内部表 有唯一索引/高选择性非唯 一索引时效率好，否则效 率会很低。驱动表很大， 不如用其他方式； b) NL特有优点是：可先返 回已经连接的行，而不必 等待所有的连接操作处理 完才返回数据，这可以实 现快速的响应时间。 a)非等值连接效率较高 b)关联列都有索引效果好 c) 两大表做连接比NL好 d) 如果最终返回的数据 量过大，sort_area_size有限，会导致使用TEMP表空间排序的I/O过多，使数据库性能下降。 哈希连接HJ (Hash Join) 嵌套循环NL (Nested Loops) 排序合并连接SMJ (Sort Merge Join) 使用提示指定表连接 (1)CBO/统计数字/等值(2)HASH_AREA_SIZE 外部表小， 内部表索引选择性好 (1)两大表范围查询 (2)SORT_AREA_SIZE Select /*+use_nl(e,b) parallel(b,4)+/ ,hiredate,m From emp e,bonus b Where e.ename=b.ename; 嵌套循环NL (Nested Loops) Select /*+use_hash(e,b) parallel(b,4)+/ ,hiredate,m From emp e,bonus b Where e.ename=b.ename; Select /*+use_merge(e,b) parallel(e,4)parallel(b,4)+/ ,hiredate,m From emp e,bonus b Where e.ename=b.ename; 哈希连接HJ (Hash Join) 排序合并连接SMJ (Sort Merge Join) SQL语句，执行计划，表访问方式之间的关系 * SQL源代码 完全表访问 全表 扫描