晚上抽空看了看ask tom的RSS,发现两篇应该说很入门的关于IN/EXISTS的文章:http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074,http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684 文章很长,也没有仔细看完,不过有些东西还是很有意思的,动手实验了一下。大家随便看看咯,Tom当初回答问题的环境我已经没办法测试了,在10.1.0.4下做了些测试,CBO和RBO(使用hints)下还是很大区别的。SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10已选择14行。执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=1218) 1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=1218)SQL> create table tmp_emp as select * from scott.emp where ename like 'S%';表已创建。SQL> select * from tmp_emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=174) 1 0 TABLE ACCESS (FULL) OF 'TMP_EMP' (TABLE) (Cost=3 Card=2 Bytes=174)先测试一下IN:SQL> select * from tmp_emp where ename in (select ename from scott.emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=188) 1 0 HASH JOIN (SEMI) (Cost=7 Card=2 Bytes=188) 2 1 TABLE ACCESS (FULL) OF 'TMP_EMP' (TABLE) (Cost=3 Card=2 Bytes=174) 3 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=98)表没有做过分析,使用了HASH JOIN (SEMI)。Tom在回答问题的时候提到:Select * from T1 where x in ( select y from T2 )is typically processed as:select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;The subquery is evaluated, distincted, indexed (or hashed or sorted) and then joined to the original table -- typically.显然第二个查询是很奇怪的。也正是这个促使我打开了数据库测试,难道一个IN还需要先DISTINCT一下?没见过IN产生排序操作啊。SQL> ed已写入 file afiedt.buf 1 select * from tmp_emp,(select distinct ename from scott.emp) t 2* where tmp_emp.ename=t.enameSQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ENAME---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 SMITH 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 SCOTT执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=2 Bytes=188) 1 0 VIEW (Cost=8 Card=2 Bytes=188) 2 1 SORT (UNIQUE) (Cost=8 Card=2 Bytes=202) 3 2 HASH JOIN (Cost=7 Card=2 Bytes=202) 4 3 TABLE ACCESS (FULL) OF 'TMP_EMP' (TABLE) (Cost=3 Card=2 Bytes=188) 5 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=98)显然不一样了。那么改用RULE模式:SQL> ed已写入 file afiedt.buf 1 select /*+ rule */ * from tmp_emp,(select distinct ename from scott.emp) t 2* where tmp_emp.ename=t.enameSQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ENAME---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 SCOTT 7369 SMITH CLERK 7902 17-12月-80 800 20 SMITH执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 MERGE JOIN 2 1 VIEW 3 2 SORT (UNIQUE) 4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) 5 1 SORT (JOIN) 6 5 TABLE ACCESS (FULL) OF 'TMP_EMP' (TABLE)再试试加上索引:SQL> alter table tmp_emp add constraints pk_tmpemp primary key(ename);表已更改。分析一下:SQL> analyze table tmp_emp compute statistics for table for all indexed columns;表已分析。SQL> analyze table scott.emp compute statistics for table for all columns;表已分析。SQL> select * 2 from tmp_emp e,(select distinct ename from scott.emp) t 3 where e.ename=t.ename; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ENAME---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 SMITH 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 SCOTT执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=2 Bytes=188) 1 0 VIEW (Cost=6 Card=2 Bytes=188) 2 1 SORT (UNIQUE) (Cost=6 Card=2 Bytes=88) 3 2 NESTED LOOPS (Cost=5 Card=2 Bytes=88) 4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=70) 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_EMP' (TABLE) (Cost=1 Card=1 Bytes=39) 6 5 INDEX (UNIQUE SCAN) OF 'PK_TMPEMP' (INDEX (UNIQUE)) (Cost=0 Card=1)SQL> ed已写入 file afiedt.buf 1 select /*+rule*/ * 2 from tmp_emp e,(select distinct ename from scott.emp) t 3* where e.ename=t.enameSQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ENAME---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 SCOTT 7369 SMITH CLERK 7902 17-12月-80 800 20 SMITH执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 NESTED LOOPS 2 1 VIEW 3 2 SORT (UNIQUE) 4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_EMP' (TABLE) 6 5 INDEX (UNIQUE SCAN) OF 'PK_TMPEMP' (INDEX (UNIQUE))SQL> select * from tmp_emp where ename in (select ename from scott.emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7369 SMITH CLERK 7902 17-12月-80 800 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=44) 1 0 NESTED LOOPS (Cost=5 Card=1 Bytes=44) 2 1 SORT (UNIQUE) (Cost=3 Card=14 Bytes=70) 3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=70) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_EMP' (TABLE) (Cost=1 Card=1 Bytes=39) 5 4 INDEX (UNIQUE SCAN) OF 'PK_TMPEMP' (INDEX (UNIQUE)) (Cost=0 Card=1)SQL> select /*+rule*/ * from tmp_emp where ename in (select ename from scott.emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7369 SMITH CLERK 7902 17-12月-80 800 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 NESTED LOOPS 2 1 VIEW OF 'VW_NSO_1' (VIEW) 3 2 SORT (UNIQUE) 4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_EMP' (TABLE) 6 5 INDEX (UNIQUE SCAN) OF 'PK_TMPEMP' (INDEX (UNIQUE))这次确实接近了很多,总体上看和Tom说的情况差不多。不过RBO这种模式对后边的表对应列选择性低时应该很好,而其他情况恐怕不见得是优化的。下面看看EXISTS:SQL> select * from tmp_emp t where exists(select null from scott.emp e where t.ename=e.ename); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7369 SMITH CLERK 7902 17-12月-80 800 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=44) 1 0 NESTED LOOPS (Cost=5 Card=1 Bytes=44) 2 1 SORT (UNIQUE) (Cost=3 Card=14 Bytes=70) 3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=70) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_EMP' (TABLE) (Cost=1 Card=1 Bytes=39) 5 4 INDEX (UNIQUE SCAN) OF 'PK_TMPEMP' (INDEX (UNIQUE)) (Cost=0 Card=1)看着和用IN一样哦。SQL> select /*+rule*/ * from tmp_emp t where exists(select null from scott.emp e where t.ename=e.ename); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'TMP_EMP' (TABLE) 3 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE)用RBO就不同咯!SQL> select t.* from tmp_emp t,scott.emp e where t.ename=e.ename; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20执行计划---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=2 Bytes=88) 1 0 NESTED LOOPS (Cost=5 Card=2 Bytes=88) 2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=70) 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_EMP' (TABLE) (Cost=1 Card=1 Bytes=39) 4 3 INDEX (UNIQUE SCAN) OF 'PK_TMPEMP' (INDEX (UNIQUE)) (Cost=0 Card=1)这样看来在CBO下,使用内关联、IN、EXISTS很可能得到同一个执行计划(更多的情况就不测试了),优化器会发现三者的语义是相同的;而在较早的RBO下EXISTS采用FILTER而IN相当于对子查询先DISTINCT后关联,内关联则是直接关联就行了。Tom在他最新的回复中这样说:Use the RBO and see what you see. way back when I wrote this, that was the "more popular" of the two perhapstoday in 2005, what I said years ago using the RBO does not apply to the CBO. the cbo is smart enough to recognize these two things are effectively the same.IN相当于对子查询先DISTINCT后关联这一条真的没有想通,RBO为什么做这样的事呢?实在是没有普适性,我个人的理解就是设计的时候认为IN后面是跟一个值列表的情况居多,当然先把值算出来,然后NL就可以了,如果后面是一个表里的值那也就一样处理;而且使用IN的时候也许大多是子查询对外层查询的筛选性高,即外层的表较大,而子查询的返回值较少。看看不同情况的不同执行计划,Oracle在CBO上确实还是花了点心思的,赫赫。结论:在RBO下,使用IN还是EXISTS需要视情况而定,只要记住使用IN存在排序和DISTINCT这一步骤应该就不难判断;CBO下优化器会为你选择,怎么写就只是习惯问题了。
CBO与RBO下的IN/EXISTS
时间: 2024-09-17 03:54:04
CBO与RBO下的IN/EXISTS的相关文章
Oracle中的优化器--CBO和RBO
Oracle中的优化器--CBO和RBO Oracle数据库中的优化器又叫查询优化器(Query Optimizer).它是SQL分析和执行的优化工具,它负责生成.制定SQL的执行计划.Oracle的优化器有两种,基于规则的优化器(RBO)与基于代价的优化器(CBO) RBO: Rule-Based Optimization 基于规则的优化器 CBO: Cost-Based Optimization 基于代价的优化器 RBO自ORACLE 6以来被采用,一直沿
mysql下的”not exists ( b except A)”解决办法
数据库系统概论第六版中文版中的51页,有个"not exists ( b except A)" 的例子,要求查询"找出选修了 Biology 系开设的所有课程的学生".实验平台搭建去我博客搜索 书上的sql 命令如下: select S.ID , S.name from student as S where not exists (( select course_id from course where dept_name = 'Biology') except (
【性能优化】CBO,RBO在ORACLE中的应用
ORACLE 提供了CBO.RBO两种SQL优化器.CBO在ORACLE7 引入,但在ORACLE8i 中才成熟.ORACLE已经明确声明在ORACLE9i之后的版本中 (ORACLE 10G ),RBO将不再支持.CBO和 RBO作为不同的SQL优化器,对SQL语句的执行计划产生重大影响,如果要对现有的应用程序从RBO向 CBO移植,则必须充分考虑这些影响,避免SQL语句性能急剧下降:但是,对新的应用系统,则可以考虑直接使用CBO,在CBO模式下进行SQL语 句编写.分析执行计划.性能测
ORACLE优化器RBO与CBO介绍总结
RBO和CBO的基本概念 Oracle数据库中的优化器又叫查询优化器(Query Optimizer).它是SQL分析和执行的优化工具,它负责生成.制定SQL的执行计划.Oracle的优化器有两种,基于规则的优化器(RBO)与基于代价的优化器(CBO) RBO: Rule-Based Optimization 基于规则的优化器 CBO: Cost-Based Optimization 基于代价的优化器 RBO 自ORACLE 6以来被采用,一直沿用至ORA
Oracle CBO几种基本的查询转换详解
Oracle CBO几种基本的查询转换详解 查询转换(Query Transformation),又称为逻辑优化(Logical Optimization),也称为软优化,即查询转换器在逻辑上对语句做一些语义等价转换,从而能使优化器生成效率更高的执行计划. 语句在被提交到Oracle后,解析器(Parser)会对SQL语句的语法.语义进行分析,并将查询中的视图展开.划分为小的查询块(Query Block).它们是嵌套或者相互关联的,而查询形式则决定了它们之间是如何关联的.这些查询块被传送给了查
Oracle优化器CBO的知识点
ORACLE 提供了基于成本(CostBased)和基于规则(RuleBased)两种优化器,简称为CBO和RBO,用于确定查询操作的执行计划. 一.如何使用CostBased优化器优化查询操作? 如何使用CBO,那么首先要理解这些概念 1.CBO的成本计算的依据 (1)统计信息:与SQL语句所引用的对象相关以及主机的CPU和IO (2)SQL语句本身 (3).环境:例如与优化器相关的参数设置 2.优化器目标:optimizer_mode (1)ALL_ROWS (2)FIRST_ROWS_N
SQL Server-聚焦IN VS EXISTS VS JOIN性能分析(十九)
前言 本节我们开始讲讲这一系列性能比较的终极篇IN VS EXISTS VS JOIN的性能分析,前面系列有人一直在说场景不够,这里我们结合查询索引列.非索引列.查询小表.查询大表来综合分析,简短的内容,深入的理解,Always to review the basics. IN VS EXISTS VS JOIN性能分析 我们继续创建测试表,如下 CREATE SCHEMA [compare] CREATE TABLE t_outer ( id INT NOT NULL PRIMARY KEY,
ORACLE性能优化之SQL语句优化
文章来源:http://blog.csdn.net/jdzms23/article/details/23850783 版权声明:本文为博主原创文章,未经博主允许不得转载. 目录(?)[-] SQL语句执行过程 1 SQL语句的执行步骤 2 典型SELECT语句完整的执行顺序 3 SQL语句执行过程 优化器及执行计划 1 SQL优化方法论 合理应用Hints 1Hints 索引及应用实例 1什么是索引 2索引分类 3什么时候使用索引 4改写SQL使用索引 5索引应用 其他优化技术及应用 1其他优化
给PLSQL插上飞翔的翅膀-PLSQL优化
60-80% of database performance issues are related to poorly performing SQL,60-80%的数据库性能问题要归结于生产中糟糕的SQL语句! 以此一文来总结笔者近10多年来的工作经验并基于最基本的也是最有效的对于Oracle数据库中的RBO.CBO.索引.WHERE条件进行讲解同时配以大量案例来帮助读者从此文中学到的相关的理论知识快速的运用到其正在从事的生产环境中的优化过程中去. 优化的理论基础 通过Select Count(