1. 压缩索引的执行计划
create index IDX_I_LLDBC_1 on I_LLDBC (BCZIP) tablespace WII compress;

SQL> select bytes/1024/1024 from dba_segments t where t.segment_name='IDX_I_LLDBC_1';

BYTES/1024/1024
---------------
             88
            
SQL> explain plan for select * from i_lldbc where bczip='201706';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1383564320
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   338 | 44954 |   200   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| I_LLDBC       |   338 | 44954 |   200   (0
|*  2 |   INDEX RANGE SCAN          | IDX_I_LLDBC_1 |   338 |       |     3   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("BCZIP"='201706')
  
2. 如果使用的是非压缩索引
SQL> create index IDX_I_LLDBC_1 on I_LLDBC (BCZIP) tablespace WII nologging;

Index created

SQL> select bytes/1024/1024 from dba_segments t where t.segment_name='IDX_I_LLDBC_1'; 

BYTES/1024/1024        --空间比压缩模式将近大了一倍
---------------
            144

SQL> explain plan for select * from i_lldbc where bczip='201706';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1383564320
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   338 | 44954 |   200   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| I_LLDBC       |   338 | 44954 |   200   (0
|*  2 |   INDEX RANGE SCAN          | IDX_I_LLDBC_1 |   338 |       |     3   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("BCZIP"='201706')

14 rows selected

--其中执行计划中的Rows=338是如下来的：(select count(distinct BCZIP) from i_lldbc) / (select count(*) of i_lldbc)
SQL> select 7300624/21546 from dual;

7300624/21546
-------------
338.838949224

-- Bytes=44954 的是如下了来的: 338*(select AVG_ROW_LEN from user_tables a where a.table_name='I_LLDBC')
SQL> select 133*338 from dual;  --行数*行的长度

   133*338
----------
     44954

综述：索引压缩后，表现得执行计划的数值是一样的。

--创建非压缩的bitmap index
create bitmap index IDX_I_LLDBC_1 on I_LLDBC (BCZIP) tablespace WII nologging;
--大小已经变小了很多
SQL> select bytes/1024/1024 from dba_segments t where t.segment_name='IDX_I_LLDBC_1';

BYTES/1024/1024
---------------
             21

SQL> EXPLAIN PLAN FOR  select * from i_lldbc where bczip='201706';

Explained

--从执行计划看出，Rows 和 Bytes没有改变，还是按照原来的算法算出来的。
  但是Cost 由 200 变为了77, 明显减少了，说明性能得到了优化。
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2122468186
--------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   338 | 44954 |    77   (
|   1 |  TABLE ACCESS BY INDEX ROWID | I_LLDBC       |   338 | 44954 |    77   (
|   2 |   BITMAP CONVERSION TO ROWIDS|               |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE | IDX_I_LLDBC_1 |       |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("BCZIP"='201706')            

--创建压缩的 bitmap index  : 报错, 因为bitmap已经是默认按compress的方式存储的。
create bitmap index IDX_I_LLDBC_1 on I_LLDBC (BCZIP) tablespace WII compress nologging;

事实上，数据选择率为1/338=3%, 还是bitmap比tree index 更为有效。