一.创建表并insert数据
createtableta(idnumber,namevarchar2(10));
createtabletb(idnumber,jobvarchar2(10));
begin
foriin1..1000000loop
begin
insertintotavalues(i,'dave');
commit;
end;
endloop;
end;
begin
foriin1..1000000loop
begin
ifi<10then
insertintotbvalues(i,'boy');
elsifi<20andi>10then
insertintotbvalues(i,'girl');
commit;
endif;
end;
endloop;
end;
二.在没有索引的情况关联ta和tb查询
相关链接:
Oracle Optimizer CBO RBO
http://blog.csdn.net/xujinyang/article/details/6832662
多表连接的三种方式详解HASH JOIN MERGE JOIN NESTED LOOP
http://blog.csdn.net/xujinyang/article/details/6832659
Oracle Hint
http://blog.csdn.net/xujinyang/article/details/6973341
2.1optimizer选择CBO(10g默认)
--ta在前
selectta.id,ta.name,tb.jobfromta,tbwhereta.id=tb.id;
--tb在前
selectta.id,ta.name,tb.jobfromtb,tawhereta.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索引后在查看
--建索引
createindexidx_ta_idonta(id);
createindexidx_tb_idontb(id);
--tb在前
selectta.id,ta.name,tb.jobfromtb,tawhereta.id=tb.id;
--ta在前
selectta.id,ta.name,tb.jobfromta,tbwhereta.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.jobfromta,tbwhereta.id=tb.id;
SYS@anqing2(rac2)>select /*+rule*/ta.id, ta.name,tb.job from ta,tb where ta.id<100 andta.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.jobfromtb,tawhereta.id=tb.id;
总结:
这2个就区别很明显。因为Oracle对sql的解析是从后向前的。那么当先遇到tb时,那么会对tb进行全表扫描,然后用这个结果匹配ta。因为ta有索引,所以通过索引去匹配。
如果先遇到ta,那么就会对ta进行全表扫描。因为2个表的差距很大,所以全表扫描的成本也就很大。
所以在RBO下,大表在前,小表在后。这样就会先遇到小表,后遇到大表。如果有指定限定的where条件,会先走限定条件的表。
2.4 drop索引之后,在走RBO
dropindexidx_ta_id;
dropindexidx_tb_id;
--ta在前
select/*+rule*/ta.id,ta.name,tb.jobfromta,tbwhereta.id=tb.id;
--tb在前
select/*+rule*/ta.id,ta.name,tb.jobfromtb,tawhereta.id=tb.id;
总结:
这里选择了Sort Merge Join来连接2张表。Sort Merge join用在没有索引,并且数据已经排序的情况.
我们表中的记录是按照顺序插叙的,所以符合这个条件。SQL的解析还是按照从后往前,所以这里ta和tb在前先扫描的顺序不一样,不过都是全表扫描。效率都不高。
2.5引深一个问题:使用字段名代替*
*能方便很多,但在ORACLE解析的过程中,会通过查询数据字典,会将’*’依次转换成所有的列名,这就需要耗费更多的时间.从而降低了效率。
SYS@anqing2(rac2)> set timing on
SYS@anqing2(rac2)> select * from ta where rownum=1;
ID NAME
---------- ----------
1 dave
Elapsed: 00:00:00.03
SYS@anqing2(rac2)> desc ta
NameNull?Type
----------------------------------------- -------- ----------------------------
IDNUMBER
NAMEVARCHAR2(10)
SYS@anqing2(rac2)> select id,name from ta where rownum=1;
ID NAME
---------- ----------
1 dave
Elapsed: 00:00:00.02
时间已经缩短。但不明显,用Toad来查看一下:
写全字段,执行时间是161毫秒,用*是561毫秒。差距很明显。
查看一下他们的执行计划:
SYS@anqing2(rac2)>select * from ta where rownum=1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 761731071
---------------------------------------------------------------------------
| id| operation| name | rows| bytes | cost (%cpu)| time|
---------------------------------------------------------------------------
|0 | select statement||1 |20 |7(72)| 00:00:01 |
|*1 |count stopkey||||||
|2 |table access full| ta|890k|16m|7(72)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement
SYS@anqing2(rac2)> select id,name from ta where rownum=1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 761731071
---------------------------------------------------------------------------
| id| operation| name | rows| bytes | cost (%cpu)| time|
---------------------------------------------------------------------------
|0 | select statement||1 |20 |7(72)| 00:00:01 |
|*1 |count stopkey||||||
|2 |table access full| ta|890k|16m|7(72)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement
注意:
使用*和 写全字段名,他们的执行计划是一样的,但是执行时间不一样。
-------------------------------------------------------------------------------------------------------