Oracle 多表连接顺序与性能关系测试

一. 创建表并insert 数据create table ta （id number,name varchar2（10））;create table tb（id number,job varchar2（10））; beginfor i in 1..1000000 loopbegininsert into ta values（i,"dave"）;commit;end;end loop;end; beginfor i in 1..1000000 loopbeginif i<10 theninsert into tb values（i,"boy"）;elsif i<20 and i>10 theninsert into tb values（i,"girl"）;commit;end if;end;end loop;end; 二.在没有索引的情况关联ta 和 tb 查询 2.1 optimizer选择 CBO（10g 默认）--ta 在前select ta.id, ta.name,tb.job from ta,tb where ta.id=tb.id; --tb 在前select ta.id, ta.name,tb.job from tb,ta where ta.id=tb.id; 总结：两条SQL 执行计划是一样的， ta和tb 的顺序没有影响。因为ta和tb 的记录相差较大，ta是100万，tb 只有20条。所以这里CBO 选择使用Hash Join。 CBO 选择2个表中记录较小的表tb，将其数据放入内存，对Join key构造hash 表，然后去扫描大表ta。找出与散列表匹配的行。 2.2 对ta和tb 的ID 建b-tree 索引后在查看 --建索引create index idx_ta_id on ta（id）;create index idx_tb_id on tb（id）; --tb 在前select ta.id, ta.name,tb.job from tb,ta where ta.id=tb.id; --ta 在前select ta.id, ta.name,tb.job from ta,tb where ta.id=tb.id; 总结：执行计划还是一样，不同的是表之间的关联模式发生的改变，从Hash Join 变成了Nested Loops。 Nested loop一般用在连接的表中有索引，并且索引选择性较好的时候. 在我们这个示例中，CBO 选择把返回结果集较小的表tb 作为outer table，CBO 下，默认把outer table 作为驱动表，然后用outer table 的每一行与inner table（我们这里是ta）进行Join，去匹配结果集。由此可见，在tb（inner table）有索引的情况，这种匹配就非常快。这种情况下整个SQL的cost： cost = outer access cost + （inner access cost * outer cardinality）从某种角度上看，可以把Nested loop 看成2层for 循环。 2.3 使用RBO 查看在10g里，optimizer 默认已经使用CBO了，如果我们想使用RBO，只能通过Hint 来实现。 -- ta 在前select /*+rule*/ta.id, ta.name,tb.job from ta,tb where ta.id=tb.id; SYS@anqing2（rac2）> select /*+rule*/ta.id, ta.name,tb.job from ta,tb where ta.id<100 and ta.id=tb.id;Elapsed: 00:00:00.00-- 注意这个SQL里，我们加了ta.id<100 的条件Execution Plan----------------------------------------------------------Plan hash value: 3943212106 ---------------------------------------------------| Id | Operation | Name |---------------------------------------------------| 0 | SELECT STATEMENT | || 1 | TABLE ACCESS BY INDEX ROWID | TB || 2 | NESTED LOOPS | || 3 | TABLE ACCESS BY INDEX ROWID| TA ||* 4 | INDEX RANGE SCAN | IDX_TA_ID ||* 5 | INDEX RANGE SCAN | IDX_TB_ID |----------------------------------------------------- 当我们加上条件之后，就先走ta了，而不是tb。因为先走ta，用ta的限制条件过滤掉一部分结果，这样剩下的匹配工作就会减少。Predicate Information （identified by operation id）:--------------------------------------------------- 4 - access（"TA"."ID"<100） 5 - access（"TA"."ID"="TB"."ID"） Note----- - rule based optimizer used （consider using cbo） --tb 在前select /*+rule*/ta.id, ta.name,tb.job from tb,ta where ta.id=tb.id; 总结：这2个就区别很明显。因为Oracle对sql的解析是从后向前的。那么当先遇到tb时，那么会对tb进行全表扫描，然后用这个结果匹配ta。因为ta有索引，所以通过索引去匹配。如果先遇到ta，那么就会对ta进行全表扫描。因为2个表的差距很大，所以全表扫描的成本也就很大。所以在RBO 下，大表在前，小表在后。这样就会先遇到小表，后遇到大表。如果有指定限定的where 条件，会先走限定条件的表。

MySQL自动备份及灾难恢复使用Oacle数据泵命令行交互模式相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）