INDEX_JOIN
这里就以INDEX_JOIN为例,简单描述一下如何影响Oracle的执行计划的产生。
控制执行计划最简单的方法莫过于使用HINT,这篇文章要介绍的是,在不使用HINT的情况下,让Oracle产生INDEX_JOIN执行计划。
下面先构造查询所用的表,问题中使用的表是HR用户下的EMPLOYEES。如果hr用户不存在,Oracle9i可以通过$ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建用户。
SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES;
表已创建。
SQL> SELECT COUNT(*) FROM EMPLOYEES;
COUNT(*)
----------
107
已选择 1 行。
查询语句为:
SQL> SET AUTOT TRACE
SQL> SELECT EMPLOYEE_ID, SALARY
2 FROM EMPLOYEES
3 WHERE SALARY > 2000;
已选择107行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
需要采用索引连接,首先将两个索引建立起来。而且索引连接是CBO才能采用的执行计划,因此对表和索引进行分析。
SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID);
表已更改。
SQL> CREATE INDEX IND_EMP_SALARY ON EMPLOYEES (SALARY);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE => TRUE)
PL/SQL 过程已成功完成。
再次执行上面的SQL:
SQL> SELECT EMPLOYEE_ID, SALARY
2 FROM EMPLOYEES
3 WHERE SALARY > 2000;
已选择107行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=107 Bytes=856)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=856)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
Oracle执行的仍然是全表扫描,但是优化器以及是CBO了。先通过HINT,看看现在是否已经满足了INDEX_JOIN执行计划的执行条件:
SQL> SELECT /*+ INDEX_JOIN(EMPLOYEES PK_EMPLOYEES IND_EMP_SALARY) */
2 EMPLOYEE_ID, SALARY
3 FROM EMPLOYEES
4 WHERE SALARY > 2000;
已选择107行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=107 Bytes=856)
1 0 VIEW OF 'index$_join$_001' (Cost=4 Card=107 Bytes=856)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'IND_EMP_SALARY' (NON-UNIQUE) (Cost=3 Card=107 Bytes=856)
4 2 INDEX (FAST FULL SCAN) OF 'PK_EMPLOYEES' (UNIQUE) (Cost=3 Card=107 Bytes=856)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
现在Oracle执行INDEX_JOIN的条件已经满足,Oracle之所以没有选择INDEX_JOIN而选择全表扫描,是因为Oracle认为全表扫描的代价比INDEX_JOIN要低。
不使用HINT,而让Oracle选择INDEX_JOIN,就必须让Oracle认为全表扫描的代价比INDEX_JOIN要高。
最直接的办法是修改Oracle收集的统计信息,通过这种方法来使Oracle认为表扫描的搭建远远大于通过索引连接的代价。
SQL> SELECT NUM_ROWS, BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'EMPLOYEES';
NUM_ROWS BLOCKS
---------- ----------
107 2
现在的统计信息显示,全部数据存储在两个BLOCK中,Oracle当然认为全部扫描的代价低,如果设置表统计信息中BLOCK的数量很大,Oracle就会认识到全表扫描的代价比较大。
SQL> EXEC DBMS_STATS.SET_TABLE_STATS(USER, 'EMPLOYEES', NUMROWS => 100, NUMBLKS => 100)
PL/SQL 过程已成功完成。
SQL> SELECT EMPLOYEE_ID, SALARY
2 FROM EMPLOYEES
3 WHERE SALARY > 2000;
已选择107行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=100 Bytes=800)
1 0 VIEW OF 'index$_join$_001' (Cost=4 Card=100 Bytes=800)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'IND_EMP_SALARY' (NON-UNIQUE) (Cost=3 Card=100 Bytes=800)
4 2 INDEX (FAST FULL SCAN) OF 'PK_EMPLOYEES' (UNIQUE) (Cost=3 Card=100 Bytes=800)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2273 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed
通过设置Oracle的统计信息的方法,让目标执行计划的代价最小,从而使得Oracle选择了预期的执行计划。
转载 http://yangtingkun.itpub.net/post/468/357542
最新文章
- Activity系列讲解---Activity运行时的屏幕方向,全屏,窗体模式的设置
- 说说怎么写clean code
- OAuth 2 Developers Guide--reference
- [Mime] QuotedPrintableEncoding帮助类 (转载)
- Poj 3368 Frequent values
- Spring 入门 Ioc-Annotation
- [ An Ac a Day ^_^ ] [kuangbin带你飞]专题六 最小生成树 POJ 1251	Jungle Roads
- CodeForces 610C Harmony Analysis
- ES6-字符串的扩展-模板字符串
- Java方法的概念及使用
- .NET Core跨平台的奥秘[下篇]:全新的布局
- Python——字符串、文件操作,英文词频统计预处理
- 《笨方法学Python》加分题20
- MySQL-查询结果缓存
- InnerClass annotations are missing corresponding EnclosingMember annotations. Such InnerClas...
- md5加密,同样的代码得到不同的加密结果(已解决)
- Smallest Difference(暴力全排列)
- 什么是 CLR(转)
- 数据库实例: STOREBOOK >; 用户 >; 编辑 用户: DBSNMP
- jdbc 小结
热门文章
- NET在SQL Server中的图片存取技术
- hibernate配置之<;property name=";hbm2ddl.auto";>;create<;/property>;导致每次创建SessionFactory都清空数据库中的数据
- Seajs demo
- HDU 4620 Fruit Ninja Extreme 搜索
- Uubuntu 14.04 LTS反编译apk
- windows下安装python,安装框架django。
- mtk android lcm调试
- BZOJ 1494 生成树计数(生成树计数-矩阵)
- 数据库系统中事务的ACID原则
- nginx fastcgi php-fpm的关系梳理