ITPub博客

首页 > 数据库 > Oracle > or与索引

or与索引

原创 Oracle 作者:stilllovekk 时间:2016-07-02 11:42:41 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-30

  • 博文量
    20
  • 访问量
    29432