1.表连接的 语法
1.1 语句
left|right join on 关联条件
1.2 符号
on 驱动表字段 = 查找表字段(+)
——oracle 表连接 的符号写法
select a.*,b.* from tab_a a,tab_b b
where a.depart_id = b.depart_id(+); --等价于 tab_a a left join tab_b b on a.depart_id = b.depart_id
select a.*,b.* from tab_a a,tab_b b
where a.depart_id(+) = b.depart_id; --等价于 tab_a a right join tab_b b on a.depart_id = b.depart_id
2.表连接的 连接方式
排序-合并连接（Sort Merge Join,SMJ）
嵌套循环（Nested Loop,NL）
哈希连接（Hash Join,HJ）
迪卡尔积（Cartesian product）
HINT:
1. /*+ MERGE(TABLE)*/
1. /*+ NO_MERGE(TABLE)*/
1. /*+ USE_MERGE(TABLE)*/
1. /*+ USE_NL(TABLE)*/
1. /*+ USE_HASH(TABLE)*/
1. /*+ DRIVING_SITE(TABLE)*/
--构建测试表和数据
create table t_depart(
depart_no varchar2(32),
depart_name varchar2(64)
);
alter table t_depart
add constraints pk_t_depart primary key(depart_no);
create table t_emp(
emp_no varchar2(32),
depart_no varchar2(32),
emp_name varchar2(100),
emp_salary decimal(9,2)
);
alter table t_emp
add constraints pk_t_emp primary key(emp_no);
alter table t_emp
add constraints fk_t_emp foreign key(depart_no) references t_depart(depart_no);
insert /*+ append*/ into ……
create index idx_t_emp_depart_no on t_emp(depart_no);
--查看 统计信息
select * from user_tables where table_name in ();
--计算 统计信息
exec dbms_stats.gather_table_stats(ownname => 'ZZHTEST',tabname=>'T_EMP',method_opt=>'for all columns size auto');
exec dbms_stats.gather_table_stats(ownname => 'ZZHTEST',tabname=>'T_DEPART',method_opt=>'for all columns size auto');
--测试
select e.emp_no,d.depart_name
from t_emp e,t_depart d
where d.depart_no = e.depart_no
and e.emp_no ='E001';
--SMJ,NL,HJ
select /*+ use_merge(e,d)*/ e.emp_no,d.depart_name
from t_emp e,t_depart d
where d.depart_no = e.depart_no
and e.emp_no ='E001';
select /*+ use_nl(e,d)*/ e.emp_no,d.depart_name
from t_emp e,t_depart d
where d.depart_no = e.depart_no
and e.emp_no ='E001';
select /*+ use_hash(e,d)*/ e.emp_no,d.depart_name
from t_emp e,t_depart d
where d.depart_no = e.depart_no
and e.emp_no ='E001';
--迪卡尔积
select e.emp_no,d.depart_name
from t_emp e,t_depart d
where e.emp_no ='E001';
3.表连接的 驱动顺序
32.蛤蟆读书笔记收获不止Oracle之表连接的驱动顺序 http://wenku.baidu.com/view/5a600a91581b6bd97e19ea26.html?from=search
【小结】嵌套循环 和 哈希连接 有驱动顺序，驱动表的顺序不同将影响表连接的性能;而 排序合并连接 没有驱动表的概念，无论那张表在前都无妨。
SMJ:先分别SORT，再MERGE，所以 没有驱动表的概念。
NL: 外层循环，内层循环，所以有 驱动表
HJ:hash table 和 bitmap,所以有 驱动表
HINT:
1. /*+ ORDER*/
2. /*+ LEADING(TABLE)*/
【实验】表连接的驱动顺序
步骤1. 构建 大表，小表
create table t_sale_info
as
select
add_months(sysdate,floor(dbms_random.value(-24,12))) statis_date,
t1.emp_no,
t2.brand_code,
floor(dbms_random.value(100,5000)) sale_cnt
from t_emp t1,td_brand t2
connect by level <= 2;
select count(*) from t_sale_info; --7832
select count(*) from td_brand; --8
步骤2. 分别指定 NL,HJ,SMJ 的驱动表，执行表连接;查看执行计划，比较 Buffers 和 Used-Men 。
sqlplus zzhtest/zzhtest@ssa_234
show serveroutput;
show autotrace;
set pagesize 100;
set linesize 1000;
set timing on;
alter session set statistics_level = all;
select /*test*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
select /*test0101*/ /*+ leading(t1) use_nl(t2)*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
select /*test0102*/ /*+ leading(t2) use_nl(t1)*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
select /*test0201*/ /*+ leading(t1) use_hash(t2)*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
select /*test0202*/ /*+ leading(t2) use_hash(t1)*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
select /*test0301*/ /*+ leading(t1) use_merge(t2)*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
select /*test0302*/ /*+ leading(t2) use_merge(t1)*/ * from t_sale_info t1,td_brand t2 where t2.brand_code = t1.brand_code and t2.brand_name = 'ONLY';
【Tips】提取执行计划:
1. 从 v$sql_plan 提取
1.1 查看 最近一次执行查询语句的 执行计划，且不能重复提取
set serveroutput off;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
select * from table(dbms_xplan.display_cursor(null,null,'all iostats last'));
select * from table(dbms_xplan.display_cursor);
1.2 查看 指定sql_id的查询语句的 执行计划
select sql_id,sql_text from v$sql where sql_text like 'select /*test*/%';
select * from table(dbms_xplan.display_cursor('<sql_id>',null,'allstats last'));
select * from table(dbms_xplan.display_cursor( '<sql_id>' ,null,'iostats last'));
select * from table(dbms_xplan.display_cursor( '<sql_id>' ,null,'all iostats last'));
2. 从 plan_table 提取:使用 explain plan 命名
explain plan for select * from dual;
select * from plan_table;
--2.1 查看 最近一次执行查询语句的 执行计划，且不能重复提取
select * from table(dbms_xplan.display_cursor);
【问题】v$sql,v$sql_plan_statistics_all,v$session,v$sql_plan 的授权问题
grant select on v$sql to zzhtest;
ORA-02030:can only select from fixed tables/views
v$sql 对应 v_$sql
v$sql_plan_statistics_all 对应 v_$sql_plan_statistics_all
v$session 对应 v_$session
v$sql_plan 对应 v_$sql_plan
v$parameter 对应 v_$parameter
说明:v$sql是synonym，v_$sql是view。
参考:
浙公网安备 33010602011771号