【SQL优化】B树索引位图转换及OR到UNION（ALL）的改写

在11g中，or操作符如果发生在索引列上，数据库则会启用BITMAP CONVERSION（B树索引位图转换），如：

SQL> select * from emp where empno=7788 or ename='SCOTT';

执行计划
----------------------------------------------------------
Plan hash value: 1272340143


--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |     2 |    78 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | EMP           |     2 |    78 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   3 |    BITMAP OR                     |               |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | PK_EMP        |       |       |     0   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IDX_EMP_ENAME |       |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   5 - access("EMPNO"=7788)
   7 - access("ENAME"='SCOTT')




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        868  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

两个索引，分别转化为位图，满足各自条件的设置位为1，如果两个索引的位都为1，则rowid保留，然后再根据rowid去回表提取记录。

我们可以改写为union：

SQL> select * from emp where empno=7788
  2  union
  3  select * from emp where ename='SCOTT';




执行计划
----------------------------------------------------------
Plan hash value: 4119156321


-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     2 |    78 |     5  (80)| 00:00:01 |
|   1 |  SORT UNIQUE                  |               |     2 |    78 |     5  (80)| 00:00:01 |
|   2 |   UNION-ALL                   |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    39 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_EMP        |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    39 |     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"=7788)
   6 - access("ENAME"='SCOTT')




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

其实个人觉得，改写为union all然后再去重更好：

SQL> select distinct empno,ename from (
  2  select empno,ename from emp where empno=7788
  3  union all
  4  select empno,ename from emp where ename='SCOTT');




执行计划
----------------------------------------------------------
Plan hash value: 707634659


------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     2 |    20 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                   |               |     2 |    20 |     4  (25)| 00:00:01 |
|   2 |   VIEW                         |               |     2 |    20 |     3   (0)| 00:00:01 |
|   3 |    UNION-ALL                   |               |       |       |            |          |
|   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 |
|   6 |     TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    10 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | IDX_EMP_ENAME |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   5 - access("EMPNO"=7788)
   7 - access("ENAME"='SCOTT')




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        486  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>

这不，union all 再distinct的资源耗费最低。

以上试验方法来自《Oracle查询优化改写-技巧与案例》一书。