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

本文通过实验展示了在SQL查询中使用UNION和DISTINCT进行优化的方法，比较了不同语法结构在执行计划、资源消耗及性能上的差异。重点在于展示如何在特定条件下选择最优查询策略，以提升查询效率。

在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>