简介
本文所叙述的现象只存在于10g 低版本数据库的优化器环境中。
1、创建实验环境
数据库version 10.2.0.1.0
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
--使用sample schema下的emp
SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
--在ename列建索引
SQL> create index idx_emp_ename on emp(ename);
Index created.
--息查看emp表上的索引信
SQL> select a.index_name, a.table_owner, a.table_name from user_indexes a where a.table_name = 'EMP';
INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ----------------------------- ------------------------------
PK_EMP SCOTT EMP
IDX_EMP_ENAME SCOTT EMP
--查看对应的索引列值信息
SQL> col column_name for a15
SQL> select b.index_name, b.table_name, b.column_name from user_ind_columns b where b.table_name = 'EMP';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ----------------------------- ---------------
PK_EMP EMP EMPNO
IDX_EMP_ENAME EMP ENAME
--屏蔽掉b_tree_bitmap_plans
SQL> alter session set "_b_tree_bitmap_plans" = false;
Session altered.
2、优化问题的sql
-- 查看问题sql的执行计划
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7900 OR ename = 'JAMES';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 20 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7900 OR "ENAME"='JAMES')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
可以看到当前的sql走了full table scan,而对应empno列上是建有主键索引PK_EMP的,那么该不该走索引呢?我们来看看
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) FROM emp WHERE empno = 7900 OR ename = 'JAMES';
COUNT(*)
----------
1
从empno列的基数上看来,这里走索引效率会更高而实际却走了全表。下面对源sql进行改写为
SELECT empno, ename FROM emp WHERE empno = 7900
UNION
SELECT empno, ename FROM emp WHERE ename = 'JAMES';
--再次查看 修改后sql的执行计划;
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7900
UNION
SELECT empno, ename FROM emp WHERE ename = 'JAMES';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4119156321
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 40 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"=7900)
6 - access("ENAME"='JAMES')
Note
-----
- dynamic sampling used for this statement
23 rows selected.
--至此,执行计划得到了修正。
结论
对于遇or优化器无法正确选取执行计划的问题,在11g R2版本中的优化器里已经得到了改善,由此看来oracle也是在不断完善自己的。
*************************************************** 11g R2 测试环境 *******************************************************
--以下是在11g R2环境中做的测试
1、创建实验环境
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/scott
Connected.
--在ename列建索引
SQL> create index idx_emp_ename on emp(ename);
Index created.
--息查看emp表上的索引信
SQL> select a.index_name, a.table_owner, a.table_name from user_indexes a where a.table_name = 'EMP';
INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ----------------------------- ------------------------------
PK_EMP SCOTT EMP
IDX_EMP_ENAME SCOTT EMP
--查看对应的索引列值信息
SQL> col column_name for a15
SQL> select b.index_name, b.table_name, b.column_name from user_ind_columns b where b.table_name = 'EMP';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ----------------------------- ---------------
PK_EMP EMP EMPNO
IDX_EMP_ENAME EMP ENAME
--屏蔽掉b_tree_bitmap_plans
SQL> alter session set "_b_tree_bitmap_plans" = false;
Session altered.
2、查看对应sql的执行计划
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7900 OR ename = 'JAMES';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2037299637
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 20 | 3 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ENAME"='JAMES')
4 - filter(LNNVL("ENAME"='JAMES'))
5 - access("EMPNO"=7900)
19 rows selected.
-- END
本文所叙述的现象只存在于10g 低版本数据库的优化器环境中。
1、创建实验环境
数据库version 10.2.0.1.0
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
--使用sample schema下的emp
SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
--在ename列建索引
SQL> create index idx_emp_ename on emp(ename);
Index created.
--息查看emp表上的索引信
SQL> select a.index_name, a.table_owner, a.table_name from user_indexes a where a.table_name = 'EMP';
INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ----------------------------- ------------------------------
PK_EMP SCOTT EMP
IDX_EMP_ENAME SCOTT EMP
--查看对应的索引列值信息
SQL> col column_name for a15
SQL> select b.index_name, b.table_name, b.column_name from user_ind_columns b where b.table_name = 'EMP';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ----------------------------- ---------------
PK_EMP EMP EMPNO
IDX_EMP_ENAME EMP ENAME
--屏蔽掉b_tree_bitmap_plans
SQL> alter session set "_b_tree_bitmap_plans" = false;
Session altered.
2、优化问题的sql
-- 查看问题sql的执行计划
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7900 OR ename = 'JAMES';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | EMP | 1 | 20 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7900 OR "ENAME"='JAMES')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
可以看到当前的sql走了full table scan,而对应empno列上是建有主键索引PK_EMP的,那么该不该走索引呢?我们来看看
![](http://blog.itpub.net/kindeditor/plugins/emoticons/images/0.gif)
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) FROM emp WHERE empno = 7900 OR ename = 'JAMES';
COUNT(*)
----------
1
从empno列的基数上看来,这里走索引效率会更高而实际却走了全表。下面对源sql进行改写为
SELECT empno, ename FROM emp WHERE empno = 7900
UNION
SELECT empno, ename FROM emp WHERE ename = 'JAMES';
--再次查看 修改后sql的执行计划;
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7900
UNION
SELECT empno, ename FROM emp WHERE ename = 'JAMES';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4119156321
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 40 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"=7900)
6 - access("ENAME"='JAMES')
Note
-----
- dynamic sampling used for this statement
23 rows selected.
--至此,执行计划得到了修正。
结论
对于遇or优化器无法正确选取执行计划的问题,在11g R2版本中的优化器里已经得到了改善,由此看来oracle也是在不断完善自己的。
*************************************************** 11g R2 测试环境 *******************************************************
--以下是在11g R2环境中做的测试
1、创建实验环境
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/scott
Connected.
--在ename列建索引
SQL> create index idx_emp_ename on emp(ename);
Index created.
--息查看emp表上的索引信
SQL> select a.index_name, a.table_owner, a.table_name from user_indexes a where a.table_name = 'EMP';
INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ----------------------------- ------------------------------
PK_EMP SCOTT EMP
IDX_EMP_ENAME SCOTT EMP
--查看对应的索引列值信息
SQL> col column_name for a15
SQL> select b.index_name, b.table_name, b.column_name from user_ind_columns b where b.table_name = 'EMP';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ----------------------------- ---------------
PK_EMP EMP EMPNO
IDX_EMP_ENAME EMP ENAME
--屏蔽掉b_tree_bitmap_plans
SQL> alter session set "_b_tree_bitmap_plans" = false;
Session altered.
2、查看对应sql的执行计划
SQL> explain plan for SELECT empno, ename FROM emp WHERE empno = 7900 OR ename = 'JAMES';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2037299637
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 20 | 3 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ENAME"='JAMES')
4 - filter(LNNVL("ENAME"='JAMES'))
5 - access("EMPNO"=7900)
19 rows selected.
-- END
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26727294/viewspace-1775794/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26727294/viewspace-1775794/