[转]通过分析SQL语句的执行计划优化SQL (七)[4]
来源:互联网 发布:appcleaner mac 破解 编辑:程序博客网 时间:2024/06/05 10:39
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
我们这个查询的意思是让A、C表做NL连接,并且让A表作为内表,但是从执行计划来看,没有达到我们的目的。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
对对象进行分析后:
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=336)
1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)
2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)
2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
转自 http://it.educity.cn/oracle/200712281702191014.htm- [转]通过分析SQL语句的执行计划优化SQL (七)[4]
- [转]通过分析SQL语句的执行计划优化SQL (七)[2]
- [转]通过分析SQL语句的执行计划优化SQL (七)[1]
- [转]通过分析SQL语句的执行计划优化SQL (七)[3]
- [转]通过分析SQL语句的执行计划优化SQL (七)[5]
- 通过分析SQL语句的执行计划优化SQL (七)
- 通过分析SQL语句的执行计划优化SQL(七)(2)
- 通过分析SQL语句的执行计划优化SQL(七)(1)
- [转]通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL(三)
- 通过分析SQL语句的执行计划优化SQL(五)
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL(总结)
- 通过分析SQL语句的执行计划优化SQL
- C#2.0类和对象学习系列之构造函数
- 二分法插入排序
- [转]通过分析SQL语句的执行计划优化SQL (七)[3]
- RGB/YUV Pixel Conversion
- 07年10大CEO言论
- [转]通过分析SQL语句的执行计划优化SQL (七)[4]
- [转]通过分析SQL语句的执行计划优化SQL (七)[5]
- WaitForMultipleObjects
- 关于“This file has been modified outside of the source editor. Do you want to reload it?”错误的处理
- 实战JBossMQ JMS集群
- 安装新的nt9中断 动态 显示26字母,"ESC"改变颜色
- [转]oracle 使用leading, use_nl, rownum调优例子
- 2008 挂机 赚钱
- [新概念三] Lesson 4 - The double life of Alfred Bloggs