深入理解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/Linuxhr@ORCL> conn sh/sh

linwaterbin

2832人浏览 · 2013-02-24 21:37:23

linwaterbin · 2013-02-24 21:37:23 发布

环境：

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

Linux

更多推荐

网卡速率和双工模式的配置

http://linux.chinaitlab.com/system/792187.html1、mii-tool 配置网络设备协商方式的工具； 1.1 mii-tool 介绍； mii-tool - view, manipulate media-independent interface status （mii-tool 是查看，管理介质的网络接口的状态）

Linux

Linux虚拟文件系统之文件系统卸载（sys_umount())

Linux中卸载文件系统由umount系统调用实现，入口函数为sys_umount()。较于文件系统的安装较为简单，下面是具体的实现。1. /*sys_umont系统调用*/2. SYSCALL_DEFINE2(umount, char __user *, name, int, flags)3. {4.struct path path;

Linux

Linux系统下超级终端Minicom的使用方法（例如：连接交换机，路由器等）转http://baike.baidu.com/view/2911642.htm?fr=ala0_1

Linux系统下超级终端Minicom的使用方法 　　Linux下的Minicom的功能与下的超级终端功能相似，适于在通过超级终端对设备的管理以及对嵌入操作系统的升级，现写出Minicom的使用手册： 　　1．启动minicom 　　以root权限登录系统 　　使用命令 　　minicom –s 则minicom启动，屏

Linux

所有评论(0)

查看更多评论

linwaterbin

@linwaterbin

已为社区贡献11条内容