ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何让Oracle产生预期的执行计划(二)

如何让Oracle产生预期的执行计划(二)

原创 Linux操作系统 作者:yangtingkun 时间:2007-08-12 00:00:00 0 删除 编辑

在测试执行计划、SQL优化、对比不同执行计划的效率时,让Oracle产生预期的执行计划还是很有意义的。

前两天在PUB上看到一个帖子,问为什么一个查询没有产生INDEX_JOIN执行计划。http://www.itpub.net/showthread.php?s=&threadid=829318

这里就以INDEX_JOIN为例,简单描述一下如何影响Oracle的执行计划的产生。

介绍另一种改变执行计划的方法。

如何让Oracle产生预期的执行计划(一):http://yangtingkun.itpub.net/post/468/357542


上一篇文章介绍了利用DBMS_STATS包来设置统计信息的方法,其实通过修改表结构可以达到同样的目的。

SQL> DROP TABLE EMPLOYEES;

表已丢弃。

SQL> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES;

表已创建。

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> SET AUTOT TRACE
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

首先重新建立EMPLOYEES表,收集统计信息,目前的执行计划以及恢复为全表扫描。

为了让Oracle选择INDEX_JOIN,就要使得表的访问代价增加,通过给表添加列的方法就可以解决这个问题。

SQL> ALTER TABLE EMPLOYEES ADD
2 (
3 COL1 CHAR(2000) DEFAULT '1'
4 );

表已更改。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES', CASCADE => TRUE)

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=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执行计划。

通过HINT强制全表扫描,发现物理是COST还是实际逻辑读,全表扫描都比INDEX_JOIN差。

SQL> SELECT /*+ FULL(EMPLOYEES) */
2 EMPLOYEE_ID, SALARY
3 FROM EMPLOYEES
4 WHERE SALARY > 2000;

已选择107行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=107 Bytes=856)
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=5 Card=107 Bytes=856)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
2610 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69384/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10486773