深入理解Oracle索引（7）：用实验数据观察从B-tree索引→复合索引→Bitmap索引所消费的CPU和I/O

环境：

sys@ORCL> !sqlplus -v

SQL*Plus: Release 10.2.0.1.0 - Production


sys@ORCL> !uname -a
Linux Think 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

hr@ORCL> conn sh/sh
Connected.
sh@ORCL> select index_name from user_indexes where table_name='CUSTOMERS';

INDEX_NAME
------------------------------
CUSTOMERS_MARITAL_BIX
CUSTOMERS_YOB_BIX
CUSTOMERS_PK
CUSTOMERS_GENDER_BIX

sh@ORCL> drop index CUSTOMERS_MARITAL_BIX;

Index dropped.

sh@ORCL> drop index CUSTOMERS_YOB_BIX;

Index dropped.

sh@ORCL> drop index CUSTOMERS_GENDER_BIX;

Index dropped.

sh@ORCL> drop index CUSTOMERS_PK;
drop index CUSTOMERS_PK
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


sh@ORCL> create index idx_cust_gender on customers (cust_gender);

Index created.

sh@ORCL> create index idx_cust_postal_code on customers (cust_postal_code);

Index created.

sh@ORCL> create index idx_cust_credit_limit on customers (cust_credit_limit);

Index created.
     
sh@ORCL> set autot trace

sh@ORCL> ed
Wrote file afiedt.buf

  1  select c.*
  2    from customers c
  3   where cust_gender = 'M' and
  4         cust_postal_code = '40804' and
  5*        cust_credit_limit = 10000
  6  

sh@ORCL> /

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3555343875

----------------------------------------------------------------------------------------------------
------

| Id  | Operation                        | Name                  | Rows  | Bytes | Cost (%CPU)| Time
     |

----------------------------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT                 |                       |     6 |  1080 |    69   (3)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID     | CUSTOMERS             |     6 |  1080 |    69   (3)| 00:0
0:01 |

|   2 |   BITMAP CONVERSION TO ROWIDS    |                       |       |       |            |
     |

|   3 |    BITMAP AND                    |                       |       |       |            |
     |

|   4 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |
     |

|*  5 |      INDEX RANGE SCAN            | IDX_CUST_POSTAL_CODE  |    89 |       |     1   (0)| 00:0
0:01 |

|   6 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |
     |

|*  7 |      INDEX RANGE SCAN            | IDX_CUST_CREDIT_LIMIT |    89 |       |    14   (0)| 00:0
0:01 |

|   8 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |
     |

|*  9 |      INDEX RANGE SCAN            | IDX_CUST_GENDER       |    89 |       |    52   (2)| 00:0
0:01 |

----------------------------------------------------------------------------------------------------
------


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

   5 - access("CUST_POSTAL_CODE"='40804')
   7 - access("CUST_CREDIT_LIMIT"=10000)
   9 - access("CUST_GENDER"='M')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
       2974  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

       Cost:205
       逻辑读：91

sh@ORCL> set autot off
sh@ORCL> select index_name from user_indexes where table_name='CUSTOMERS';

INDEX_NAME
------------------------------
IDX_CUST_GENDER
IDX_CUST_POSTAL_CODE
IDX_CUST_CREDIT_LIMIT
CUSTOMERS_PK

sh@ORCL> drop index IDX_CUST_GENDER;

Index dropped.

sh@ORCL> drop index IDX_CUST_POSTAL_CODE;

Index dropped.

sh@ORCL> drop index IDX_CUST_CREDIT_LIMIT;

Index dropped.

sh@ORCL> create index idx_cust_gen_pos_cred on customers (cust_gender,cust_postal_code,cust_credit_limit);

Index created.

sh@ORCL> set autot trace

sh@ORCL> ed
Wrote file afiedt.buf

  1  select c.*
  2    from customers c
  3   where cust_gender = 'M' and
  4         cust_postal_code = '40840' and
  5*        cust_credit_limit = 10000
  6  

sh@ORCL> /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3306790926

----------------------------------------------------------------------------------------------------
-

| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time
|

----------------------------------------------------------------------------------------------------
-

|   0 | SELECT STATEMENT            |                       |     6 |  1080 |     8   (0)| 00:00:01
|

|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS             |     6 |  1080 |     8   (0)| 00:00:01
|

|*  2 |   INDEX RANGE SCAN          | IDX_CUST_GEN_POS_CRED |     7 |       |     1   (0)| 00:00:01
|

----------------------------------------------------------------------------------------------------
-


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

   2 - access("CUST_GENDER"='M' AND "CUST_POSTAL_CODE"='40840' AND "CUST_CREDIT_LIMIT"=10000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1694  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

       Cost：17
       逻辑读：2

sh@ORCL> set autot off
sh@ORCL> select index_name from user_indexes where table_name='CUSTOMERS';

INDEX_NAME
------------------------------
IDX_CUST_GEN_POS_CRED
CUSTOMERS_PK

sh@ORCL> drop index IDX_CUST_GEN_POS_CRED;

Index dropped.

sh@ORCL> create bitmap index idx_cust_gender on customers(cust_gender);

Index created.

sh@ORCL> create bitmap index idx_cust_postal_code on customers (cust_postal_code);

Index created.

sh@ORCL> create bitmap index idx_cust_credit_limit on customers(cust_credit_limit);

Index created.

sh@ORCL> ed
Wrote file afiedt.buf

  1  select c.*
  2    from customers c
  3   where cust_gender = 'M' and
  4         cust_postal_code = '40804' and
  5*        cust_credit_limit = 10000
  6  

sh@ORCL> /

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 724066067

----------------------------------------------------------------------------------------------------
--

| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time
 |

----------------------------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT             |                       |     6 |  1080 |     4   (0)| 00:00:01
 |

|   1 |  TABLE ACCESS BY INDEX ROWID | CUSTOMERS             |     6 |  1080 |     4   (0)| 00:00:01
 |

|   2 |   BITMAP CONVERSION TO ROWIDS|                       |       |       |            |
 |

|   3 |    BITMAP AND                |                       |       |       |            |
 |

|*  4 |     BITMAP INDEX SINGLE VALUE| IDX_CUST_POSTAL_CODE  |       |       |            |
 |

|*  5 |     BITMAP INDEX SINGLE VALUE| IDX_CUST_CREDIT_LIMIT |       |       |            |
 |

|*  6 |     BITMAP INDEX SINGLE VALUE| IDX_CUST_GENDER       |       |       |            |
 |

----------------------------------------------------------------------------------------------------
--


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

   4 - access("CUST_POSTAL_CODE"='40804')
   5 - access("CUST_CREDIT_LIMIT"=10000)
   6 - access("CUST_GENDER"='M')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       2974  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

       Cost：8
       逻辑读：15