or与索引

or与索引


据说or会使索引失效，那么是不是呢？测试一下，每个例子后会跟随相应的解释。


初始：empno列有索引，ename没有
scott@ORA> select empno,ename from emp where  empno=7788 or empno=7369;


     EMPNO ENAME
---------- ----------
      7369 SMITH
      7788 SCOTT




Execution Plan
----------------------------------------------------------
Plan hash value: 2355049923


---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    40 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


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


   3 - access("EMPNO"=7369 OR "EMPNO"=7788)


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        658  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
啊，走了索引了，不是说会失效么？其实是这样么？不是的。因为这个or连接的两个条件全是一个字段的，or相当于两个条件的合集，两个条件都是可以走索引的，当然可以走了。
scott@ORA> select ename,empno from emp where empno=7788 or ename='SMITH';


ENAME           EMPNO
---------- ----------
SMITH            7369
SCOTT            7788




Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    40 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


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


   1 - filter("EMPNO"=7788 OR "ENAME"='SMITH')


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        658  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
啊，这个不走索引了，为什么呢？还是那句话，因为or相当于合集，如果第一个条件走索引，那么第二个条件得走全表扫描，还不如直接来一次全表扫描。


既然这样，那我如果给ename列建个索引呢？还会不会使索引失效呢？


scott@ORA> create index idx_emp_ename on emp(ename) online;


Index created.


scott@ORA> select ename,empno from emp where empno=7788 or ename='SMITH';


ENAME           EMPNO
---------- ----------
SMITH            7369
SCOTT            7788




Execution Plan
----------------------------------------------------------
Plan hash value: 1272340143


--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | EMP           |     2 |    40 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   3 |    BITMAP OR                     |               |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | PK_EMP        |       |       |     1   (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"='SMITH')


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        658  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
          
看，又走索引了，还是没有使索引失效啊？no，不是这样的，执行计划这次走的是BITMAP CONVERSION TO ROWIDS，位图转换，这个玩意不一定是好东西，有的时候会让执行计划非常烂的。
这个东西具体怎么回事呢？以后再说，现在说简单的，他是把b-tree索引进行了bitmap转换来执行sql的，就这个转换，不言而喻，很多时候问题是很大的。通常都是禁用的。
隐含参数 _b_tree_bitmap_plans可以禁用他。




找到原因就好说了，那就禁用他再看看。
scott@ORA> alter session set "_b_tree_bitmap_plans"=false;


Session altered.


scott@ORA> set autotrace on
scott@ORA> select ename,empno from emp where empno=7788 or ename='SMITH';


ENAME           EMPNO
---------- ----------
SMITH            7369
SCOTT            7788




Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    40 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


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


   1 - filter("EMPNO"=7788 OR "ENAME"='SMITH')


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        658  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
看，全表扫描了。
那么，正常情况下，这样的我们怎么优化呢？毕竟两个字段都建了索引了啊。  是吧？可以这样
用union去改写。


scott@ORA>  select ename,empno from emp where empno=7788 
  2   union  select ename,empno from emp where ename='SMITH';


ENAME           EMPNO
---------- ----------
SCOTT            7788
SMITH            7369




Execution Plan
----------------------------------------------------------
Plan hash value: 4119156321


-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     2 |    40 |     6  (34)| 00:00:01 |
|   1 |  SORT UNIQUE                  |               |     2 |    40 |     6  (34)| 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"=7788)
   6 - access("ENAME"='SMITH')


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         15  consistent gets
          2  physical reads
          0  redo size
        658  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed
这样呢，就可以走索引了，只要你索引选择性可以，肯定是比全表好的多的。另外，为什么用union 而不用union all呢？因为union all是并集就不是合集了，union是去重的。


另外，光用union有的时候也是不行的。比如：我要查工资小于1000或者是10部门的人都有什么工作。
scott@ORA> SELECT JOB FROM EMP WHERE SAL<1000 OR deptno = 10;


JOB
---------
CLERK
MANAGER
PRESIDENT
CLERK
CLERK


scott@ORA> SELECT JOB FROM EMP WHERE SAL<1000 UNION 
  2  SELECT JOB FROM EMP WHERE deptno = 10;


JOB
---------
CLERK
MANAGER
PRESIDENT
看，明显的，不该给我去重的，他也给去了。这样该怎么做的？
其实只需要多处理一下，比如加个唯一标志，然后加一层再把它去掉就行了，不过这个得是唯一的啊，不然又出错了。
rowid，主键，有唯一约束的列，反正只要能保证唯一。 都行


scott@ORA> select job from (
  2  SELECT rowid r, JOB FROM EMP WHERE SAL<1000 UNION 
  3  SELECT rowid r, JOB FROM EMP WHERE deptno = 10 );


JOB
---------
CLERK
MANAGER
CLERK
PRESIDENT
CLERK


收工


 

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

转载于:http://blog.itpub.net/30123160/viewspace-2121370/