关于ORACLE做HASH JOIN的问题 如果把HASH JOIN时读入内存的小表叫做驱动表,那么在做HASH JOIN时,是否驱动表一定是做TABLE ACCESS FULL? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 不一定对,也可读索引的吧。(未曾测试,不敢下结论)这里的小表,是个相对的概念。 比如A表有1KW,B表有50W,那么CBO选择B做驱动表。但是B表的记录数也不少,要根据数据以及索引情况判断是走全表扫描 谢谢你的回复,我是发现我的SQL做HASH JOIN的时候第一张表(就是要读入内存的表)都是做HASH JOIN,有些疑问。不知道有没有谁知道详细点的。 当然不会全是做fts了。读入内存做hash处理小表也只是把需要的数据读入内存。做个实验:OPER@tl> create table test as select * from dba_objects;表已创建。OPER@tl> create table test2 as 2 select * from dba_objects 3 where rownum<=5000;表已创建。OPER@tl> create index ind_test on test(object_id);索引已创建。OPER@tl> create index ind_test2 on test2(object_id);索引已创建。OPER@tl> exec dbms_stats.gather_table_stats('OPER','TEST',cascade=>true)PL/SQL 过程已成功完成。OPER@tl> exec dbms_stats.gather_table_stats('OPER','TEST2',cascade=>true)PL/SQL 过程已成功完成。OPER@tl> set autot traceonly explainOPER@tl> select a.* 2 from test a,test2 b 3 where a.object_id=b.object_id;执行计划----------------------------------------------------------Plan hash value: 2053579432-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5000 | 473K| 164 (2)| 00:00:02 ||* 1 | HASH JOIN | | 5000 | 473K| 164 (2)| 00:00:02 || 2 | INDEX FAST FULL SCAN| IND_TEST2 | 5000 | 20000 | 4 (0)| 00:00:01 || 3 | TABLE ACCESS FULL | TEST | 50033 | 4544K| 159 (2)| 00:00:02 |-----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")OPER@tl> select a.* 2 from test a,test2 b 3 where a.object_id=b.object_id 4 and b.object_id in(20,44,28,15);执行计划----------------------------------------------------------Plan hash value: 118016711-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | 388 | 8 (0)| 00:00:01 ||* 1 | HASH JOIN | | 4 | 388 | 8 (0)| 00:00:01 || 2 | INLIST ITERATOR | | | | | || 3 | TABLE ACCESS BY INDEX ROWID| TEST | 4 | 372 | 6 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_TEST | 4 | | 5 (0)| 00:00:01 || 5 | INLIST ITERATOR | | | | | ||* 6 | INDEX RANGE SCAN | IND_TEST2 | 4 | 16 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A"."OBJECT_ID"="B"."OBJECT_ID") 4 - access("A"."OBJECT_ID"=15 OR "A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=28 OR "A"."OBJECT_ID"=44) 6 - access("B"."OBJECT_ID"=15 OR "B"."OBJECT_ID"=20 OR "B"."OBJECT_ID"=28 OR "B"."OBJECT_ID"=44)OPER@tl> 2个Oracle 数据之间数据的转换,当转换数据量比较大的时候的效率问题? orcale超过最大连接数的问题 ---------建立表分区后,一般都把对应的index也分区么?---------- 海量数据查询SQL想优化一下,请教牛人~~~~~~~ 使用SQL实现循环,在线等~~ 备份没有权限? 一个update语句,请大家看看 jsp orcale 的连接问题。--------在线等! 如何用sql实现从一个表条件查询,然后结果直接插入另一张表?一条语句实现。 请教有关优化报表代码的方法 一个比较简单的PL/SQL语句的问题 优化SQL语句
做个实验:
OPER@tl> create table test as select * from dba_objects;表已创建。OPER@tl> create table test2 as
2 select * from dba_objects
3 where rownum<=5000;表已创建。OPER@tl> create index ind_test on test(object_id);索引已创建。OPER@tl> create index ind_test2 on test2(object_id);索引已创建。OPER@tl> exec dbms_stats.gather_table_stats('OPER','TEST',cascade=>true)PL/SQL 过程已成功完成。OPER@tl> exec dbms_stats.gather_table_stats('OPER','TEST2',cascade=>true)PL/SQL 过程已成功完成。OPER@tl> set autot traceonly explain
OPER@tl> select a.*
2 from test a,test2 b
3 where a.object_id=b.object_id;执行计划
----------------------------------------------------------
Plan hash value: 2053579432-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 473K| 164 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 5000 | 473K| 164 (2)| 00:00:02 |
| 2 | INDEX FAST FULL SCAN| IND_TEST2 | 5000 | 20000 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST | 50033 | 4544K| 159 (2)| 00:00:02 |
-----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")OPER@tl> select a.*
2 from test a,test2 b
3 where a.object_id=b.object_id
4 and b.object_id in(20,44,28,15);执行计划
----------------------------------------------------------
Plan hash value: 118016711-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 388 | 8 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 388 | 8 (0)| 00:00:01 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 4 | 372 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST | 4 | | 5 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN | IND_TEST2 | 4 | 16 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
4 - access("A"."OBJECT_ID"=15 OR "A"."OBJECT_ID"=20 OR "A"."OBJECT_ID"=28 OR
"A"."OBJECT_ID"=44)
6 - access("B"."OBJECT_ID"=15 OR "B"."OBJECT_ID"=20 OR "B"."OBJECT_ID"=28 OR
"B"."OBJECT_ID"=44)OPER@tl>