深入理解Oracle索引（7）：用實驗資料觀察從B-tree索引→複合索引→Bitmap索引所消費的CPU和I/O

環境：

sys@ORCL> !sqlplus -vSQL*Plus: Release 10.2.0.1.0 - Productionsys@ORCL> !uname -aLinux 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/shConnected.sh@ORCL> select index_name from user_indexes where table_name='CUSTOMERS';INDEX_NAME------------------------------CUSTOMERS_MARITAL_BIXCUSTOMERS_YOB_BIXCUSTOMERS_PKCUSTOMERS_GENDER_BIXsh@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 keysh@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 tracesh@ORCL> edWrote 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:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID     | CUSTOMERS             |     6 |  1080 |    69   (3)| 00:00: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:00:01 ||   6 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |     ||*  7 |      INDEX RANGE SCAN            | IDX_CUST_CREDIT_LIMIT |    89 |       |    14   (0)| 00:00:01 ||   8 |     BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |     ||*  9 |      INDEX RANGE SCAN            | IDX_CUST_GENDER       |    89 |       |    52   (2)| 00:00: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 offsh@ORCL> select index_name from user_indexes where table_name='CUSTOMERS';INDEX_NAME------------------------------IDX_CUST_GENDERIDX_CUST_POSTAL_CODEIDX_CUST_CREDIT_LIMITCUSTOMERS_PKsh@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 tracesh@ORCL> edWrote 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 selectedExecution 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 offsh@ORCL> select index_name from user_indexes where table_name='CUSTOMERS';INDEX_NAME------------------------------IDX_CUST_GEN_POS_CREDCUSTOMERS_PKsh@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> edWrote 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