oracle10g SQL优化之遇or改union

简介

      本文所叙述的现象只存在于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

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

转载于:http://blog.itpub.net/26727294/viewspace-1775794/