云贝教育 | 【技术文章】SQL优化之位图索引

作者：炎燚小寳

原文链接：http://www.tdpub.cn/Blog/detail/id/1290.html

前言

有个朋友问我这个SQL还有没有优化空间，当前跑的状态不理想。

一、SQL原型

SELECT COUNT("UID") AS REMAINEDNUM, 1 AS "LEVEL", ORGUID AS "UID"
  FROM EMPSMS.RPT_WAIT_B
 WHERE ORGUID > 0
 GROUP BY ORGUID;
其中ORGUID只有0和1，其中1有1000W数据，0有1000条

二、模拟数据

create table  RPT_WAIT_B as select * from dba_objects where owner in('SYS','CDH19C')
insert into RPT_WAIT_B  select * from  RPT_WAIT_B
commit;
update RPT_WAIT_B set object_id=1 where owner='SYS';
commit;
update RPT_WAIT_B set object_id=0 where owner='CDH19C';
commit;
SQL> select OBJECT_ID,count(1) from RPT_WAIT_B group by OBJECT_ID;
 OBJECT_ID   COUNT(1)
---------- ----------
0       1408
 1    4840832

三、创建两个索引

create index idx_OBJECT_ID_OWNER on RPT_WAIT_B(OBJECT_ID,owner) ;

drop index idx_OBJECT_ID_OWNER;
create bitmap index idx_bit_OBJECT_ID_OWNER on RPT_WAIT_B(OBJECT_ID,owner) ;

四、测试结果如下
测试全表扫描

SQL>  SELECT /*+ full(RPT_WAIT_B)*/  COUNT(owner) AS REMAINEDNUM, 1 AS "LEVEL", OBJECT_ID AS "UID"
  2    FROM RPT_WAIT_B
  3   WHERE OBJECT_ID > 0
  4   GROUP BY OBJECT_ID;
 
Elapsed: 00:00:06.75
Execution Plan
----------------------------------------------------------
Plan hash value: 3996458411
---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |  5094K|   145M| 18709   (2)| 00:03:45 |
|   1 |  HASH GROUP BY     |            |  5094K|   145M| 18709   (2)| 00:03:45 |
|*  2 |   TABLE ACCESS FULL| RPT_WAIT_B |  5094K|   145M| 18553   (1)| 00:03:43 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID">0)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     136522  consistent gets
      68293  physical reads
          0  redo size
        670  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--测试普通索引

SQL>   SELECT /*+ index(RPT_WAIT_B,idx_OBJECT_ID_OWNER)*/  COUNT(owner) AS REMAINEDNUM, 1 AS "LEVEL", OBJECT_ID AS "UID"
  2    FROM RPT_WAIT_B
 WHERE OBJECT_ID > 0
  3    4   GROUP BY OBJECT_ID;
Elapsed: 00:00:00.69
Execution Plan
----------------------------------------------------------
Plan hash value: 2308901301
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |  5094K|   145M| 12226   (1)| 00:02:27 |
|   1 |  SORT GROUP BY NOSORT|                     |  5094K|   145M| 12226   (1)| 00:02:27 |
|*  2 |   INDEX RANGE SCAN   | IDX_OBJECT_ID_OWNER |  5094K|   145M| 12226   (1)| 00:02:27 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">0 AND "OBJECT_ID" IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12167  consistent gets
      12155  physical reads
          0  redo size
        670  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--测试位图索引

 SQL>    SELECT  COUNT(owner) AS REMAINEDNUM, 1 AS "LEVEL", OBJECT_ID AS "UID"
  2    FROM RPT_WAIT_B
  3   WHERE OBJECT_ID > 0
  4   GROUP BY OBJECT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 292611569
--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |  5094K|   145M|   161   (1)| 00:00:02 |
|   1 |  SORT GROUP BY NOSORT        |                         |  5094K|   145M|   161   (1)| 00:00:02 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                         |  5094K|   145M|   161   (1)| 00:00:02 |
|*  3 |    BITMAP INDEX RANGE SCAN   | IDX_BIT_OBJECT_ID_OWNER |       |       |            |          |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_ID">0)
       filter("OBJECT_ID">0)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        121  consistent gets
        119  physical reads
          0  redo size
        670  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

总结：

通过以上测试，在聚合运算中，如果group by列的基数很少，使用位图索引，能极大地提升SQL性能。但位图索引适用OLAP场景，在OLTP系统中慎用，这就需要结合业务逻辑来考量优化方案。